MySQL / 系统课时

公共表表达式

公共表表达式

当一条 SQL 同时包含多层筛选、聚合和关联时,直接堆叠子查询会迅速降低可读性。公共表表达式(CTE)可以先为中间结果命名,再在后续查询中引用,让复杂查询按逻辑步骤拆开,同时保持在一条 SQL 中完成。

学习目标

  • 掌握 WITH 定义和引用公共表表达式的基本语法
  • 使用 CTE 拆分复杂查询中的中间计算步骤
  • 理解 CTE 与普通子查询的适用边界

用 WITH 命名中间结果

CTE 使用 WITH 放在主查询之前。可以把它理解成“只在当前 SQL 语句中有效的临时结果集”:先执行逻辑上的中间查询,再通过名称引用它。

假设有订单表 orders,字段包括订单编号 id、用户编号 user_id、订单金额 amount 和状态 status。现在需要统计每个用户已完成订单的总金额,并只保留累计金额不少于 1000 的用户。

sql
WITH user_sales AS (
    SELECT
        user_id,
        SUM(amount) AS total_amount
    FROM orders
    WHERE status = 'PAID'
    GROUP BY user_id
)
SELECT
    user_id,
    total_amount
FROM user_sales
WHERE total_amount >= 1000
ORDER BY total_amount DESC;

预期结果

text
+---------+--------------+
| user_id | total_amount |
+---------+--------------+
| 102     | 2350.00      |
| 101     | 1480.00      |
+---------+--------------+

实际用户编号和金额取决于表中数据。这里的关键点是:聚合逻辑集中在 user_sales 中,主查询只负责对聚合结果继续筛选和排序。

拆分多个计算阶段

一条语句可以定义多个 CTE,后面的 CTE 还可以引用前面已经定义的 CTE。这样可以把“筛选原始数据 → 聚合 → 再筛选”等阶段显式拆开,避免多层括号嵌套。

sql
WITH paid_orders AS (
    SELECT user_id, amount
    FROM orders
    WHERE status = 'PAID'
),
user_sales AS (
    SELECT
        user_id,
        SUM(amount) AS total_amount
    FROM paid_orders
    GROUP BY user_id
)
SELECT user_id, total_amount
FROM user_sales
WHERE total_amount >= 1000;

预期结果

text
+---------+--------------+
| user_id | total_amount |
+---------+--------------+
| 101     | 1480.00      |
| 102     | 2350.00      |
+---------+--------------+

这种写法的价值主要是结构清晰,而不是“定义 CTE 就一定更快”。MySQL 优化器会根据具体查询决定如何处理 CTE,因此性能仍应通过执行计划和实际数据验证。

CTE 与子查询怎么选

如果中间结果只使用一次,而且表达式很短,普通子查询通常已经足够。例如只套一层简单聚合时,为它额外命名可能反而增加阅读跳转。

当查询需要多次引用同一逻辑、存在多个连续计算阶段,或者嵌套子查询已经难以阅读时,CTE 更合适。尤其在调试复杂 SQL 时,可以先单独执行 CTE 内部的查询,确认中间结果,再继续检查主查询。

使用时需要注意的边界

普通 CTE 的作用域只覆盖紧随其后的那一条 SQL,不能像真实临时表一样被后续多条语句持续访问。它主要解决 SQL 结构组织问题,不等同于创建持久对象。

MySQL 还支持递归 CTE,可用于层级数据和序列生成,但递归查询涉及终止条件和递归次数控制,属于更特殊的场景。处理常规筛选、聚合和关联时,先把普通 CTE 用清楚通常已经足够。

当一条 SQL 的逻辑开始出现多层嵌套时,可以优先尝试把有独立业务含义的中间结果提取为 CTE;如果拆分后只是增加名字而没有降低理解成本,则继续使用简单子查询即可。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

公共表表达式

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通