MySQL / 系统课时
公共表表达式
公共表表达式
当一条 SQL 同时包含多层筛选、聚合和关联时,直接堆叠子查询会迅速降低可读性。公共表表达式(CTE)可以先为中间结果命名,再在后续查询中引用,让复杂查询按逻辑步骤拆开,同时保持在一条 SQL 中完成。
学习目标
- 掌握
WITH定义和引用公共表表达式的基本语法 - 使用 CTE 拆分复杂查询中的中间计算步骤
- 理解 CTE 与普通子查询的适用边界
用 WITH 命名中间结果
CTE 使用 WITH 放在主查询之前。可以把它理解成“只在当前 SQL 语句中有效的临时结果集”:先执行逻辑上的中间查询,再通过名称引用它。
假设有订单表 orders,字段包括订单编号 id、用户编号 user_id、订单金额 amount 和状态 status。现在需要统计每个用户已完成订单的总金额,并只保留累计金额不少于 1000 的用户。
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;预期结果
+---------+--------------+
| user_id | total_amount |
+---------+--------------+
| 102 | 2350.00 |
| 101 | 1480.00 |
+---------+--------------+实际用户编号和金额取决于表中数据。这里的关键点是:聚合逻辑集中在 user_sales 中,主查询只负责对聚合结果继续筛选和排序。
拆分多个计算阶段
一条语句可以定义多个 CTE,后面的 CTE 还可以引用前面已经定义的 CTE。这样可以把“筛选原始数据 → 聚合 → 再筛选”等阶段显式拆开,避免多层括号嵌套。
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;预期结果
+---------+--------------+
| 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;如果拆分后只是增加名字而没有降低理解成本,则继续使用简单子查询即可。