subquery(nested query)可以使用主查询中的FROM列出的表中的列

In short, a nested query works independentof the enclosing SQL statement and can make use of any of the column valuesfrom the tables listed in the enclosing statement's FROM clause. You can use nested queries to perform multi-table operations without having to JOIN rows in multiple related tables. However, if you need data values from multiple tables, or if you want individual column values and aggregate function values in the same row in the results table, you can nest a subquery with the aggregate function that you need in the SELECT clause of a multi-table query or JOIN.

SELECT    trade_date,    symbol,    shares * price AS 'Total Trade',    (        SELECT            COUNT(*)        FROM            trades        WHERE            trade_date > GETDATE() - 365        AND cust_ID = CID --CID is in Customer not the trades which is the only table in this            -- subquery's from clause    ) AS 'Count',    (        SELECT            SUM(price) * SUM(shares)        FROM            trades        WHERE            trades.trade_date >= GETDATE() - 365        AND cust_ID = CID    )                       AS 'Total $ Volume)',    CID                     AS 'Cust ID',    TRIM(f_name)+' '+l_name AS 'Customer'FROM    EI.CUSTOMERSJOIN EI.TRADESON    CID = cust_CIDWHERE    shares * price >= 100000AND trade_date >= GETDATE() -365ORDER BY    customer;