32. 递归查询（WITH RECURSIVE）

有些数据天然是「一层套一层」的：公司的组织架构、商品的分类树、帖子的评论回复。这类叫层级数据（也叫树形数据）。要一次性把某种关系「顺藤摸瓜」全查出来，普通 JOIN 做不到，得用递归查询。

递归查询建立在上一章的 CTE 之上，写成 WITH RECURSIVE。

结构：锚点 + 递归

一个递归 CTE 由两部分用 UNION（或 UNION ALL）拼起来：

1. 锚点成员（anchor）：最开始的那批数据，是递归的起点。
2. 递归成员（recursive）：它引用 CTE 自己，基于上一步的结果继续往下找。

WITH RECURSIVE 名字 (列1, 列2, ...) AS (
    -- ① 锚点：选出起点行
    SELECT ...
    UNION [ALL]
    -- ② 递归：引用「名字」本身，继续向下
    SELECT ...
    FROM 表 JOIN 名字 ON ...
)
SELECT * FROM 名字;

执行顺序是：先跑锚点得到第 0 批（R0），再反复跑递归成员，把上一批结果当输入，得到 R1、R2……直到某次返回空，递归结束。最后把所有批次 UNION 起来就是结果。整个过程就像「先把种子撒下去，再一圈圈往外长」。

示例：查某个上司的所有下属

我们用一张员工表，每个人记录自己的上级 manager_id：

CREATE TABLE employees (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    manager_id INT REFERENCES employees(id)
);

INSERT INTO employees (name, manager_id) VALUES
    ('王总',   NULL),
    ('李经理', 1),
    ('赵经理', 1),
    ('钱组长', 2),
    ('孙组长', 2),
    ('小李',   3),
    ('小王',   4);

现在要查「李经理（id=2）及其所有下属（含下属的下属）」：

WITH RECURSIVE subordinates AS (
    -- 锚点：李经理本人
    SELECT id, manager_id, name
    FROM employees
    WHERE id = 2
    UNION
    -- 递归：找直接汇报给上一步结果的人
    SELECT e.id, e.manager_id, e.name
    FROM employees e
    JOIN subordinates s ON s.id = e.manager_id
)
SELECT * FROM subordinates;

结果是李经理，加上钱组长、孙组长，加上钱组长带的小王——一共 4 行。递归做的是：第 0 批是李经理；第 1 批用 JOIN ... ON s.id = e.manager_id 找出 manager_id = 2 的人（钱、孙）；第 2 批再找 manager_id 是钱或孙的人（小王）；再往下找不到人了，返回空，结束。

终止条件在哪

递归靠「递归成员返回空结果」来终止。一旦某次迭代 JOIN 不出新行了，就自动停下，不会无限循环。所以写递归时，连接条件 ON ... 一定要能让「每一层比上一层更小」（数据范围不断收缩），最终走到叶子节点就自然结束了。

如果你忘了加终止的收缩逻辑，或者数据本身有环，PostgreSQL 会一直跑下去，直到耗尽内存或触发超时。最稳妥的兜底办法，是在查询里加一个深度列、用 WHERE 深度 < N 限制层级（见下文），或者直接用 PostgreSQL 14 起支持的 CYCLE 子句自动防环。

反向：从底层往上查路径

把连接方向反过来，就能从某个员工一路查到顶层：

WITH RECURSIVE chain AS (
    SELECT id, manager_id, name
    FROM employees
    WHERE id = 7          -- 小王
    UNION
    SELECT e.id, e.manager_id, e.name
    FROM employees e
    JOIN chain c ON c.manager_id = e.id
)
SELECT * FROM chain;

结果从「小王 → 钱组长 → 李经理 → 王总」，把整条汇报链还原出来。区别只在 ON 条件：c.manager_id = e.id 表示「当前节点的上级，去员工表里找」。

带上「层级深度」和「路径」

实际排查时，常想知道「第几层」「整条路径是什么」。可以在 CTE 里多算两列：

WITH RECURSIVE sub AS (
    SELECT id, name, manager_id, 1 AS 层级, name AS 路径
    FROM employees
    WHERE id = 2
    UNION ALL
    SELECT e.id, e.name, e.manager_id,
           s.层级 + 1,
           s.路径 || ' > ' || e.name
    FROM employees e
    JOIN sub s ON s.id = e.manager_id
)
SELECT 层级, 路径 FROM sub ORDER BY 层级;

层级 每往下一层加 1，路径 用字符串拼接把经过的人名串起来，一眼就能看出归属关系。

防止无限递归

如果担心数据里有环，可以加一个深度上限，超过就停：

WITH RECURSIVE sub AS (
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE id = 2
    UNION ALL
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    JOIN sub s ON s.id = e.manager_id
    WHERE s.depth < 10           -- 最多钻 10 层
)
SELECT * FROM sub;

这样即使真的有环，也不会无限循环，最多算到第 10 层。

其他常见用途

递归不只能查上下级。凡是「能从一个节点走到相邻节点」的图或树都适用：

• 分类目录：从某个分类往下展开所有子分类。
• 物料清单（BOM）：产品由零件组成，零件又由子零件组成。
• 评论楼中楼：一条评论下的所有回复。

核心套路都一样：锚点选起点，递归成员用「当前批」去关联「下一层」，连接条件决定往哪个方向走。

常见错误

1. 递归成员没有引用 CTE 自己，那就不是递归，会报语法错。
2. 锚点和递归成员的列数、顺序不一致，报类型或列数不匹配。
3. 连接条件写反（上下级方向搞反），查出来的层次颠倒。
4. 数据有环又没限制深度，查询跑个不停。

递归生成数字序列

递归不只能查层级，也能「造数据」。比如生成 1 到 10 的序列：

WITH RECURSIVE nums(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

锚点给 1，递归成员每次加 1，直到 n < 10 不成立停下。这种写法常用来造测试数据，或做「按天展开一段日期」之类的任务。

检测环（CYCLE）

如果数据里真有环，递归会无限跑。PostgreSQL 14 起支持 CYCLE 子句，能自动标记并终止循环：

WITH RECURSIVE sub AS (
    SELECT id, manager_id, name FROM employees WHERE id = 2
    UNION ALL
    SELECT e.id, e.manager_id, e.name
    FROM employees e JOIN sub s ON s.id = e.manager_id
)
CYCLE id SET is_cycle USING path
SELECT * FROM sub;

CYCLE id SET is_cycle USING path 会在遇到环时把 is_cycle 标为 true，并停止继续往下钻。老版本没有这个语法，就用前面讲的「深度列 + WHERE depth < N」来兜底。

一次遍历多棵树

前面的例子都从「某一个根节点」出发。如果要一次性展开所有顶层节点及其下属，只需把锚点改成「选所有没有上级的人」：

WITH RECURSIVE all_sub AS (
    SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e JOIN all_sub s ON s.id = e.manager_id
)
SELECT * FROM all_sub;

锚点用 WHERE manager_id IS NULL 抓住所有顶层（王总们），递归再各自往下钻，就能把整张组织架构一次拉平。

小结

递归 CTE = 锚点（起点） + 递归成员（引用自己往下找）。它专门对付层级、树形数据，靠「某次迭代无新行」来结束递归。写的时候，连接条件要想清楚是往「下钻」还是往「上溯」，层级关系才不会搞反。必要时带上层级深度、路径，并给深度兜底防环。