递归 CTE 是什么
WITH RECURSIVE 用“初始行 + 递归项”的方式把层级数据一层层展开：先取根节点，再用上一轮的结果连接下一层，直到没有新行为止。组织架构、分类树、物料清单、路径回溯都靠它。MySQL 8.0 与 PostgreSQL 都支持，关键词 RECURSIVE 必须写。
基本信息
| 写法 | WITH RECURSIVE 名字 AS (初始查询 UNION ALL 递归查询) |
|---|---|
| 自引用 | 递归项里 FROM 这个 CTE 名，代表上一轮新产出的行 |
| UNION ALL | 不去重、性能好；换成 UNION 会去重，可防环但代价更高 |
| 收敛方式 | 由连接条件自然收敛，或加深度上限显式刹车 |
| 深度限制 | MySQL 有 cte_max_recursion_depth，默认 1000，超出报错 |
| 列类型 | 两部分对应列的类型要一致，否则推导结果可能不符预期 |
组织架构展开
组织架构展开
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 AS lvl -- 初始行：根节点（没有上级）
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.lvl + 1 -- 递归项：引用上一轮结果
FROM employees e
JOIN org o ON e.manager_id = o.id -- 连接键需要索引
)
SELECT lvl, id, name FROM org ORDER BY lvl, id; -- 按层级输出加深度上限防跑飞
加深度上限防跑飞
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 AS lvl
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.lvl + 1
FROM employees e
JOIN org o ON e.manager_id = o.id
WHERE o.lvl < 10 -- 数据成环或关系写反时，这行能救回一条 SQL
)
SELECT lvl, id, name FROM org ORDER BY lvl; -- 超过 10 层的数据不会出现在结果里结构要点
| 组成部分 | 说明 | 注意事项 |
|---|---|---|
| 初始查询 | 取根节点，如 manager_id IS NULL | 条件写错会直接返回空集 |
| 递归项 | 连接 CTE 自身取下一层 | 连接键没索引则每轮全表扫描 |
| UNION ALL | 本轮行与历史行合并 | 结果不去重，成环时会重复展开 |
| 深度列 | 常见的 lvl 或 depth 计数列 | 没有它就无法加上限 |
| 终止条件 | 递归项返回空集即结束 | 成环时永不返回空集，必须加上限 |
典型使用场景
- 组织架构展开：统计各层级人数、列出某人全部下属
- 分类树与行政区划树的层级遍历
- 物料清单展开：从一个成品逐层算清所需零件
- 路径回溯：从某节点向上拼出到根的完整路径字符串
- 权限菜单树：按角色递归展开可见节点
- 把扁平父子表转成层级结构，供前端树组件渲染
MySQL 8.0 与 PostgreSQL 都要求写 WITH RECURSIVE，漏掉 RECURSIVE 会直接语法报错；Oracle 与 SQL Server 的写法不同，跨库脚本需要按方言分别改写。
递归项不收敛时查询不会自己结束：数据成环、父子关系写反、两人互为上级都会让语句长时间运行。上线前务必带上 WHERE lvl < N 的深度上限，并确认连接键有索引。