递归 CTE（WITH RECURSIVE）是 SQL 里专门用来处理树形与层级数据的利器。当你的表里存着”上级—下属””父评论—子评论”这种自引用关系时，一张表要展开成多级结构，传统 JOIN 写不动。本文用组织架构、评论树、连续日期三个真实场景，带你把递归 CTE 写对、写快、写稳。
一、为什么层级数据让普通 SQL 头疼
最典型的建模方式是”邻接表”（Adjacency List）：每行只记录自己的直属父节点。比如员工表加一列 manager_id，评论表加一列 parent_id。这种结构写入极其简单，但查询是噩梦——你永远不知道一条链有几层。
想查”某员工下面挂了几级团队”，用自连接（self-join）只能写死层级：JOIN 一次是一层，写三层就只能查三层，第四层就漏了。更糟的是，应用层”先查父、再查子、再查孙”的写法会产生 N+1 次查询，几百人的组织一次拉取就是几百条 SQL。递归 CTE 就是为了解决”层数未知、却要一次性展开”而生的。
二、递归 CTE 的语法骨架
2.1 锚点成员与递归成员
递归 CTE 由两段组成：先是一段”锚点”（anchor），返回递归的起点行；然后用 UNION ALL 接一段”递归成员”，这段会引用 CTE 自己，反复执行直到没有新行产生。把它念成”先取起点，再一层层往下长”就好记了。
WITH RECURSIVE cte AS (
-- 锚点成员：递归的起点
SELECT id, name, parent_id, 1 AS depth
FROM org
WHERE parent_id IS NULL -- 比如公司最高领导
UNION ALL
-- 递归成员：引用 cte 自身，逐层下钻
SELECT o.id, o.name, o.parent_id, cte.depth + 1
FROM org o
INNER JOIN cte ON o.parent_id = cte.id
)
SELECT * FROM cte ORDER BY depth, id;几个铁律：锚点段和递归段列数、类型必须一致；两段之间用 UNION ALL（比 UNION 少一次去重，性能更好，循环防护另做）；递归段里不能直接用 ORDER BY，排序要放到最外层。把骨架记住，下面三个实战都是它的变体。
三、实战一：组织架构的上下级链路
先建一张最小可用的组织表并塞几条数据，注意 manager_id 指向同一张表的 id：
CREATE TABLE org (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT REFERENCES org(id)
);
INSERT INTO org VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, '后端Leader', 2),
(5, '前端Leader', 2),
(6, '工程师A', 4),
(7, '工程师B', 4);向下展开：查出 CTO（id=2）名下的整棵子树，并标出每个人在第几层：
WITH RECURSIVE sub AS (
SELECT id, name, manager_id, 1 AS depth
FROM org
WHERE id = 2 -- 起点：CTO
UNION ALL
SELECT o.id, o.name, o.manager_id, sub.depth + 1
FROM org o
JOIN sub ON o.manager_id = sub.id
)
SELECT id, name, depth FROM sub ORDER BY depth, id;
-- 结果：CTO(1) → 后端Leader(2) → 前端Leader(2) → 工程师A(3) → 工程师B(3)向上追溯：反过来，从”工程师A”一路找到 CEO，拼出汇报链。这里把路径用 ARRAY（PostgreSQL）或字符串累积下来，结果一目了然：
WITH RECURSIVE path AS (
SELECT id, name, manager_id, ARRAY[name] AS chain
FROM org
WHERE id = 6 -- 起点：工程师A
UNION ALL
SELECT o.id, o.name, o.manager_id, path.chain || o.name
FROM org o
JOIN path ON o.id = path.manager_id
)
SELECT chain FROM path ORDER BY array_length(chain,1) DESC LIMIT 1;
-- 结果：{工程师A, 后端Leader, CTO, CEO}四、实战二：树形评论的层级展开
评论区是典型的”父—子”嵌套。表结构 comments(id, post_id, parent_id, content)，用递归 CTE 可以把一条帖子的所有评论按层级铺平，并顺手算出缩进深度：
WITH RECURSIVE tree AS (
SELECT id, parent_id, content, 0 AS depth
FROM comments
WHERE post_id = 42 AND parent_id IS NULL -- 顶层评论
UNION ALL
SELECT c.id, c.parent_id, c.content, tree.depth + 1
FROM comments c
JOIN tree ON c.parent_id = tree.id
)
SELECT
repeat(' ', depth) || content AS threaded, -- 用缩进模拟楼层
depth
FROM tree
ORDER BY depth, id;如果评论量极大（万级）且读多写少，邻接表 + 递归 CTE 每次都现算会偏慢。这时更稳的是”物化路径”（Materialized Path）：在每行直接存 /1/2/4/6 这样的路径串，查询用 LIKE '/1/2/%' 前缀匹配即可，省掉递归。但物化路径的写入和移动节点成本更高，小到中型社区用递归 CTE 完全够用。
五、实战三：生成连续日期与数字序列
递归 CTE 不止能查，还能”造”数据。没有数字辅助表时，用它生成最近 30 天、或 1 到 100 的连续序列非常方便，常用来做按天补零的统计报表：
-- 生成最近 30 天的日期序列（MySQL 8 / PostgreSQL 通用）
WITH RECURSIVE days AS (
SELECT CURDATE() AS d
UNION ALL
SELECT d - INTERVAL 1 DAY FROM days WHERE d > CURDATE() - INTERVAL 29 DAY
)
SELECT d FROM days ORDER BY d;
-- PostgreSQL 更省事，但递归写法在跨库时更通用：
-- SELECT generate_series(CURRENT_DATE - 29, CURRENT_DATE, '1 day') AS d;六、性能陷阱与循环防护
6.1 防止无限循环
递归 CTE 最怕数据里有环（比如 A 的经理是 B、B 的经理又是 A），一旦成环就会无限递归直到撑爆。各数据库的兜底方式不同：
-- PostgreSQL 14+：直接用 CYCLE 子句标记环
WITH RECURSIVE sub AS (
SELECT id, manager_id, 1 AS depth FROM org WHERE id = 2
UNION ALL
SELECT o.id, o.manager_id, sub.depth + 1
FROM org o JOIN sub ON o.manager_id = sub.id
)
CYCLE id SET is_cycle TO 'Y' DEFAULT 'N'
SELECT * FROM sub WHERE is_cycle = 'N';
-- MySQL 8：设会话级递归深度上限，超了直接报错而不是死循环
SET SESSION cte_max_recursion_depth = 100;
-- SQL Server：用 MAXRECURSION 提示
OPTION (MAXRECURSION 100);6.2 递归与索引：慢在哪
递归段每一轮都在用 parent_id = cte.id 做 JOIN，因此 parent_id（或 manager_id）这一列必须有索引，否则每一层都是全表扫描，层数一多就指数级变慢。如果一张百万行的表递归后仍然很慢，先用 PostgreSQL 慢查询优化：执行计划解读与索引调优实战 的 EXPLAIN 方法定位，再回头确认关联列索引是否生效——这本质上和 MySQL 索引底层原理 里讲的”最左前缀、覆盖索引”是同一套逻辑。
| 数据库 | 循环防护 | 递归深度限制 | 搜索排序子句 |
|---|---|---|---|
| PostgreSQL | CYCLE 子句 / 深度列 | 无硬上限（靠防护） | 支持 SEARCH BREADTH/DEPTH FIRST |
| MySQL 8 | 深度列手动判环 | cte_max_recursion_depth（默认 1000） | 不支持，靠 ORDER BY |
| SQL Server | MAXRECURSION 提示 | 默认 100，可设 0 关闭 | 不支持 |
七、MySQL 8 与 PostgreSQL 的差异对照
| 能力 | PostgreSQL | MySQL 8 |
|---|---|---|
| 基本递归 | 支持 | 支持 |
| 自动环检测 | CYCLE 子句（14+） | 需手动加深度列 |
| 递归内聚合 | 支持 | 受限（不能在递归成员聚合） |
| SEARCH 排序 | 原生支持 | 不支持 |
| 生成序列 | generate_series 更优 | 递归 CTE 是首选 |
如果你的系统横跨两种库，递归 CTE 的”基本语法”是共通的，但环检测和排序要分别适配。迁移或双写时，建议对照 PostgreSQL vs SQL Server 语法差异与迁移避坑指南 的思路，把数据库方言差异单独列一张表，避免上线才暴露。
八、递归 CTE 不是万能：何时换方案
| 方案 | 读性能 | 写/改性能 | 适用场景 |
|---|---|---|---|
| 邻接表 + 递归 CTE | 中（每次现算） | 优（只改一行） | 层级浅、写频繁、规模中小 |
| 物化路径 | 优（前缀匹配） | 中（移动节点要改串） | 读多、层级深、改动少 |
| 闭包表 | 优（直接 JOIN） | 差（维护关系表） | 读极多、写极少、超深树 |
| 嵌套集 | 优（范围查询） | 差（重构左右值） | 几乎只读的目录树 |
经验法则：评论、部门这类”写频繁、层级不超过十几层”的场景，邻接表 + 递归 CTE 是最优解；而商品类目、行政区划这类”几乎不改、但要秒开整棵树”的场景，闭包表或物化路径更合适。别一上来就为递归 CTE 加一堆优化，先想清楚读写比例。
九、落地清单
把这几条当成发布前的检查项：锚点段必须有明确的终止起点；递归段必须能”越查越少”最终收敛；生产环境务必设置递归深度上限或环检测；递归关联列（parent_id / manager_id）建好索引；层级超过 20 层且读多写少时，认真评估闭包表。和 SQL 窗口函数实战 一样，递归 CTE 也属于”学会一次、受用十年”的 SQL 进阶武器——把它和窗口函数、CTE 普通用法放在一起，你的复杂报表查询基本就通了。
