CTE 与递归 CTE
CTE 即 WITH 子句定义的命名子查询，把长查询拆成若干有名字的步骤，让 SQL 更接近阅读顺序而不是层层嵌套。递归 CTE 用 WITH RECURSIVE 把锚点结果与递归部分反复 UNION ALL，直到不再产生新行，适合展开组织架构、类目树、物料 BOM 这类自引用层级数据。MySQL 8.0 起原生支持两者。
基本信息
| 适用平台 | MySQL 8.0+、PostgreSQL、SQL Server、Oracle |
|---|---|
| 语法 | WITH name AS (SELECT ...) SELECT ... |
| 递归写法 | WITH RECURSIVE cte AS (锚点 UNION ALL 递归部分) |
| 深度限制 | cte_max_recursion_depth 默认 1000（8.0） |
| 8.0 增强 | 可加 MATERIALIZED 提示控制物化行为 |
典型使用场景
- 把多层聚合的复杂报表拆成几个可读的命名步骤
- 同一段子查询在一条语句里多次引用，避免重复书写
- 展开组织架构或类目树，求某节点的全部下级与层级深度
- 物料清单逐层展开，计算多层成本汇总
- 构造连续日期或数字序列，用于补齐报表的空缺行
基础 CTE 与递归展开
基础 CTE 与递归展开
-- 基础 CTE：先算每个用户的下单总额，再关联用户信息
WITH top_users AS (
SELECT user_id, SUM(amount) AS total
FROM orders GROUP BY user_id HAVING total > 10000
)
SELECT u.name, t.total FROM top_users t JOIN users u ON u.id = t.user_id;
-- 递归 CTE：从根节点出发逐层展开类目
WITH RECURSIVE tree AS (
SELECT id, name, parent_id, 1 AS lvl FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.lvl + 1
FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY lvl, id;
-- 生成连续日期序列，补齐报表空缺
WITH RECURSIVE d AS (
SELECT DATE '2026-10-01' AS day
UNION ALL SELECT day + INTERVAL 1 DAY FROM d WHERE day < '2026-10-31'
) SELECT * FROM d;递归 CTE 的三段结构
| 部分 | 作用 | 注意点 |
|---|---|---|
| 锚点查询 | 给出起始行 | 必须有，否则无从递归 |
| UNION ALL 递归项 | 基于上一轮结果产生新行 | 必须能收敛，列顺序与类型对齐 |
| 最终 SELECT | 读取 CTE 结果 | 可继续过滤、排序、关联 |
| 深度上限 | 防止无限递归 | cte_max_recursion_depth 默认 1000 |
CTE 只是语法层面的组织工具，不等于物化：优化器可能把非递归 CTE 合并进外层查询，也可能物化成临时结果，实际执行方式要用 EXPLAIN 看。另外 CTE 的作用域只在本条语句内，无法在后续另一条 SQL 里继续引用，需要复用应当改写成视图或临时表。
递归 CTE 默认深度上限为 1000，超出会直接报错；树中若存在环状引用，递归永远不会收敛，必须先用路径字段或层级上限做防环。