CTE 是什么
CTE（公共表表达式）用 WITH 给子查询起名字，把多层嵌套拆成可读的步骤。MySQL 8.0 与 PostgreSQL 都支持。CTE 的作用域限于同一条语句，生命周期随语句结束；MySQL 与 PostgreSQL 都可能把 CTE 物化后再使用，因此它的主要价值是可读性与结构清晰，而不是自动的性能优化。递归 CTE 用 WITH RECURSIVE 实现，可处理树形结构与层级数据。
基本信息
| 语法 | WITH 名称 AS (SELECT …) SELECT … FROM 名称 |
|---|---|
| 多个 CTE | 逗号分隔，后面的 CTE 可以引用前面的 |
| 作用域 | 仅限同一条语句，语句结束即失效 |
| 递归写法 | WITH RECURSIVE 名称 AS (初始查询 UNION ALL 递归查询) |
| 版本要求 | MySQL 8.0+ 与 PostgreSQL 8.4+ 均支持 |
| 可引用位置 | SELECT、INSERT、UPDATE、DELETE 中都可以使用 |
典型使用场景
- 拆分多层嵌套：把三层子查询改写成三个命名步骤
- 复用中间结果：同一份聚合结果在多个地方引用
- 树形结构查询：组织架构、类目层级用递归 CTE 展开
- 复杂统计分步计算：先算基础指标，再算比率与排名
- 数据清洗流程：逐层过滤与规范化，每步可单独验证
- 可读性优先的报表 SQL：用命名表达业务含义
示例
示例
WITH paid_orders AS ( # 第一步：过滤已支付订单
SELECT id, user_id, amount, created_at
FROM orders
WHERE status = 'paid'
),
user_stats AS ( # 第二步：按用户聚合
SELECT user_id,
COUNT(*) AS order_cnt,
SUM(amount) AS total_amount
FROM paid_orders
GROUP BY user_id
)
SELECT u.id, u.name, s.order_cnt, s.total_amount
FROM users u
JOIN user_stats s ON s.user_id = u.id
WHERE s.order_cnt >= 3
ORDER BY s.total_amount DESC;递归 CTE
递归 CTE
WITH RECURSIVE tree AS (
SELECT id, name, parent_id, 1 AS depth # 起点：根节点
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.depth + 1
FROM categories c
JOIN tree t ON c.parent_id = t.id # 关联上一层结果
)
SELECT * FROM tree ORDER BY depth, id; # 展开整棵层级树CTE 与子查询对照
| 对比项 | CTE | 内联子查询 |
|---|---|---|
| 可读性 | 命名后语义清晰 | 嵌套深时难读 |
| 复用 | 同一语句内可多次引用 | 需要重复书写 |
| 物化行为 | 可能被物化，注意数据量与计划 | 视优化器而定 |
| 递归支持 | 支持 WITH RECURSIVE | 不支持 |
| 作用域 | 仅当前语句 | 仅当前表达式 |
CTE 在部分版本里会被物化（MySQL 8.0 与 PostgreSQL 12+ 对可内联的 CTE 会尽量内联），性能异常时先用 EXPLAIN 确认实际计划。
后面定义的 CTE 可以引用前面定义的，反过来不行；组织顺序按数据流从上游到下游最易读。
递归 CTE 一定要有能终止的条件，否则会一直展开直到触发资源限制；层级数据存在环时会无限递归。
使用要点
- 每个 CTE 只做一件事，命名反映业务含义
- 按数据流顺序排列，从原始数据逐步加工到结果
- 先在小范围验证每一步 CTE 的输出，再组装最终查询
- 需要多次引用同一结果时用 CTE，避免重复书写同一逻辑
- 递归查询加深度上限或过滤条件，防止无限展开
- 窗口函数与 CTE 结合使用，能把分步计算写得非常清晰