WITH语句还是子查询？

原创于 2024-12-06 08:38:44 发布 · 813 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

后端开发专栏收录该内容

62 篇文章

订阅专栏

SQL中的WITH语句，也称为公用表表达式（Common Table Expressions，简称CTE），允许你定义一个临时的结果集，这个结果集可以在一个或多个SELECT、INSERT、UPDATE或DELETE语句中被引用。CTE在逻辑上类似于一个子查询，但它可以包含多个SELECT语句，并且可以进行递归查询。

一、WITH语句的基本语法

WITH cte_name (column1, column2, ...)
AS
(
    -- CTE的查询定义SELECT column1, column2, ...
    FROM ...
)
-- 主查询SELECT*FROM cte_name;

二、使用WITH语句和子查询的对比

假设有以下需求：有一个 orders表，其中包含订单ID、客户ID和订单日期，还有一个 customers表，包含客户ID和客户名称。我们想要查询每个客户在2022年的总订单金额，并且只关注那些至少有3个订单的客户

使用WITH的SQL

WITH OrderDetails AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.order_date,
        SUM(o.amount) OVER (PARTITIONBY o.customer_id) AS total_amount
    FROM
        orders o
    WHERE
        o.order_date BETWEEN'2022-01-01'AND'2022-12-31'
),
CustomerOrders AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count
    FROM
        OrderDetails
    GROUPBY
        customer_id
    HAVINGCOUNT(*) >=3
)
SELECT
    c.customer_id,
    c.name,
    od.total_amount
FROM
    CustomerOrders co
JOIN
    customers c ON co.customer_id = c.customer_id
JOIN
    OrderDetails od ON co.customer_id = od.customer_id
ORDERBY
    od.total_amount DESC;

使用子查询的SQL

SELECT
    c.customer_id,
    c.name,
    (SELECTSUM(amount) FROM orders o WHERE o.order_date BETWEEN'2022-01-01'AND'2022-12-31'AND o.customer_id = c.customer_id) AS total_amount
FROM
    customers c
WHEREEXISTS (
        SELECT1FROM orders o
        WHERE
            o.order_date BETWEEN'2022-01-01'AND'2022-12-31'AND o.customer_id = c.customer_id
        GROUPBY
            o.customer_id
        HAVINGCOUNT(*) >=3
    )
ORDERBY
    total_amount DESC;