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

最新推荐文章于 2024-04-22 09:08:20 发布

原创最新推荐文章于 2024-04-22 09:08:20 发布 · 2k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#subquery #nested #query #date #function #table

DataBase 专栏收录该内容

13 篇文章

订阅专栏

本文详细介绍了如何在SQL查询中利用嵌套查询进行多表操作，实现复杂数据筛选与聚合计算，同时展示了如何通过子查询与主查询结合，优化查询效率并简化代码结构。

In short, a nested query works independent of the enclosing SQL statement and can make use of any of the column values from 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.CUSTOMERS
JOIN EI.TRADES
ON
    CID = cust_CID
WHERE
    shares * price >= 100000
AND trade_date >= GETDATE() -365
ORDER BY
    customer;