CTE：WITH 子句与临时结果集

什么是 CTE

CTE（Common Table Expression，通用表表达式）是命名的临时结果集，存在于单个查询的执行范围内。

基本语法：

WITH cte_name AS (
    SELECT ...
)
SELECT ... FROM cte_name;

第一个 CTE

不使用 CTE（子查询）

SELECT u.name, t.total
FROM users u
JOIN (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) t ON u.id = t.user_id
WHERE t.total > 100;

使用 CTE

WITH user_totals AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
)
SELECT u.name, ut.total
FROM users u
JOIN user_totals ut ON u.id = ut.user_id
WHERE ut.total > 100;

优势：

• 命名清晰（user_totals 比子查询更易读）
• 可以被引用多次
• 逻辑分离（先计算总额，再连接用户）

多个 CTE

可以定义多个 CTE，用逗号分隔：

WITH 
user_totals AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
),
high_value_users AS (
    SELECT user_id FROM user_totals WHERE total > 500
)
SELECT u.name, ut.total
FROM users u
JOIN user_totals ut ON u.id = ut.user_id
JOIN high_value_users hvu ON u.id = hvu.user_id;

CTE 的优势

1. 提高可读性

复杂查询分解成多个命名步骤：

WITH 
monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS total
    FROM orders
    GROUP BY 1
),
sales_with_growth AS (
    SELECT 
        month,
        total,
        LAG(total) OVER (ORDER BY month) AS prev_month,
        (total - LAG(total) OVER (ORDER BY month)) / LAG(total) OVER (ORDER BY month) * 100 AS growth_pct
    FROM monthly_sales
)
SELECT * FROM sales_with_growth WHERE growth_pct > 10;

2. 避免重复计算

CTE 可以被引用多次：

WITH expensive_orders AS (
    SELECT * FROM orders WHERE amount > 500
)
SELECT 
    (SELECT COUNT(*) FROM expensive_orders) AS count,
    (SELECT AVG(amount) FROM expensive_orders) AS avg_amount,
    (SELECT SUM(amount) FROM expensive_orders) AS total_amount;

3. 模块化查询

WITH 
step1 AS (SELECT ...),
step2 AS (SELECT ... FROM step1),
step3 AS (SELECT ... FROM step2)
SELECT * FROM step3;

CTE vs 子查询 vs 临时表

特性	CTE	子查询	临时表
可读性	高	低（嵌套深）	中
可重用	单个查询内	不可重用	多个查询
性能	优化器决定	优化器决定	需要索引
作用域	当前查询	嵌套位置	会话或事务

实际应用

应用 1：分步计算

WITH 
user_stats AS (
    SELECT 
        user_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spent,
        AVG(amount) AS avg_order
    FROM orders
    GROUP BY user_id
),
user_segments AS (
    SELECT 
        user_id,
        CASE 
            WHEN total_spent > 1000 THEN 'VIP'
            WHEN total_spent > 500 THEN 'Premium'
            ELSE 'Regular'
        END AS segment
    FROM user_stats
)
SELECT u.name, us.segment, ust.total_spent
FROM users u
JOIN user_segments us ON u.id = us.user_id
JOIN user_stats ust ON u.id = ust.user_id;

应用 2：数据清洗

WITH 
cleaned_data AS (
    SELECT 
        id,
        TRIM(name) AS name,
        LOWER(email) AS email
    FROM users
    WHERE email IS NOT NULL
),
deduplicated AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM cleaned_data
)
SELECT id, name, email
FROM deduplicated
WHERE rn = 1;

应用 3：复杂过滤

WITH 
active_users AS (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
),
high_value_products AS (
    SELECT DISTINCT product 
    FROM orders 
    WHERE amount > 100
)
SELECT o.*
FROM orders o
WHERE o.user_id IN (SELECT user_id FROM active_users)
  AND o.product IN (SELECT product FROM high_value_products);

CTE 的局限

1. 不能建立索引

CTE 是临时的，不能创建索引。如果需要索引，使用临时表：

CREATE TEMP TABLE user_totals AS
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id;

CREATE INDEX ON user_totals(user_id);

2. 作用域限制

CTE 只在单个查询内有效，不能跨查询使用。

3. 优化器行为

某些数据库可能会多次计算 CTE（不像视图或临时表会物化）。PostgreSQL 12+ 支持 MATERIALIZED 关键字强制物化：

WITH MATERIALIZED expensive_calc AS (
    SELECT ... -- 复杂计算
)
SELECT * FROM expensive_calc WHERE ...;

INSERT/UPDATE/DELETE 与 CTE

CTE 可以用于 DML 语句：

WITH new_orders AS (
    SELECT user_id, product, amount
    FROM staging_orders
    WHERE validated = true
)
INSERT INTO orders (user_id, product, amount, order_date)
SELECT user_id, product, amount, CURRENT_DATE
FROM new_orders;

数据修改 CTE（PostgreSQL）：

WITH moved_rows AS (
    DELETE FROM orders
    WHERE order_date < '2025-01-01'
    RETURNING *
)
INSERT INTO archived_orders
SELECT * FROM moved_rows;

练习

1. 基本 CTE：使用 CTE 查询每个用户的订单数和总消费，然后筛选消费 > 100 的用户。

2. 多个 CTE：定义两个 CTE，一个计算用户总消费，另一个找出前 20% 的用户。

3. 替换子查询：将嵌套子查询改写为 CTE（提供一个复杂子查询示例）。

4. 分步计算：用 CTE 计算每月销售额、同比增长、并筛选增长 > 10% 的月份。

5. 数据清洗：用 CTE 清理重复数据（按邮箱去重，保留最早的记录）。

6. 链式 CTE：创建 3 个 CTE，每个依赖前一个的结果。

7. 窗口函数 + CTE：结合窗口函数和 CTE 创建销售排行榜。

8. 实际场景：用 CTE 构建用户行为分析报告（注册、首单、复购率）。

完成练习后，打开本章答案页核对。

下一步

学会了 CTE。下一节学习递归 CTE：如何处理树形结构和层级数据。

回到第四章概览 · 返回SQL 学习路线