📊 SQL 入门 Day 10：递归 CTE — 破解无限层级查询的终极武器

场景引入

你有没有遇到过这样的需求：一个组织架构表，要查出某个部门下所有层级的子部门？或者一个商品分类，要找出某个分类下的全部子分类？普通 SQL 只能查一层，需要写循环或递归代码才能搞定。

递归 CTE（WITH RECURSIVE） 就是为这类"无限层级"查询而生的，它能在一个 SQL 里完成递归遍历，堪称树形结构查询的瑞士军刀。

---

一、递归 CTE 语法结构

WITH RECURSIVE cte_name AS (
    -- 1. 非递归项（Anchor Member）：初始结果集
    SELECT ...
    FROM ...
    WHERE 条件（通常找根节点）

    UNION ALL

    -- 2. 递归项（Recursive Member）：引用 CTE 自身
    SELECT ...
    FROM cte_name
    JOIN ...
    WHERE 条件（连接子节点）
)
SELECT * FROM cte_name;

两个要点：

• 非递归项：查询起点（根节点），只执行一次
• 递归项：迭代执行，每次用上一轮结果继续查询，直到返回空行
• UNION ALL 合并每次迭代的结果（用 UNION 会去重，但通常用 ALL 更高效）

---

二、实战案例：组织架构树

表结构

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT  -- 上级 ID（NULL 表示 CEO）
);

INSERT INTO employees VALUES
(1, '张三（CEO）', NULL),
(2, '李四（技术总监）', 1),
(3, '王五（产品总监）', 1),
(4, '赵六（前端组长）', 2),
(5, '钱七（后端组长）', 2),
(6, '孙八（前端开发）', 4),
(7, '周九（前端开发）', 4),
(8, '吴十（后端开发）', 5);

递归 CTE：找出「李四」的所有下属（包括间接下属）

WITH RECURSIVE subordinates AS (
    -- 非递归项：先找到李四
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE name = '李四（技术总监）'

    UNION ALL

    -- 递归项：找上一轮结果的下属
    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY level, id;

执行结果：

id	name	manager_id	level
2	李四（技术总监）	1	1
4	赵六（前端组长）	2	2
5	钱七（后端组长）	2	2
6	孙八（前端开发）	4	3
7	周九（前端开发）	4	3
8	吴十（后端开发）	5	3

执行过程图解：

1. 第一轮：找到李四 → 返回 [id=2]
2. 第二轮：找 manager_id=2 的 → [id=4, id=5]
3. 第三轮：找 manager_id IN (4,5) 的 → [id=6, id=7, id=8]
4. 第四轮：没有人是 6/7/8 的下属 → 终止

---

三、实战案例：自顶向下路径展示

需求： 显示每个员工从 CEO 到自己的完整汇报链。

WITH RECURSIVE org_path AS (
    -- 根节点：CEO
    SELECT id, name, manager_id,
           name::TEXT AS path,
           1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id,
           op.path || ' → ' || e.name,
           op.depth + 1
    FROM employees e
    JOIN org_path op ON e.manager_id = op.id
)
SELECT id, name, depth, path
FROM org_path
ORDER BY depth, id;

执行结果：

id	name	depth	path
1	张三（CEO）	1	张三（CEO）
2	李四（技术总监）	2	张三（CEO） → 李四（技术总监）
3	王五（产品总监）	2	张三（CEO） → 王五（产品总监）
4	赵六（前端组长）	3	张三（CEO） → 李四（技术总监） → 赵六（前端组长）
...	...	...	...

---

四、实战案例：BOM（物料清单）

CREATE TABLE parts (
    part_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE bom (
    parent_id INT,
    child_id INT,
    quantity INT
);

INSERT INTO parts VALUES
(1, '电脑整机'),
(2, '主板'),
(3, 'CPU'),
(4, '内存条'),
(5, '散热器');

INSERT INTO bom VALUES
(1, 2, 1),   -- 1台电脑需要1块主板
(1, 4, 2),   -- 1台电脑需要2根内存条
(2, 3, 1),   -- 1块主板需要1颗CPU
(2, 5, 1);   -- 1块主板需要1个散热器

递归 CTE：计算每个零件所需的总数量

WITH RECURSIVE bom_explode AS (
    -- 非递归项：顶层产品（电脑整机）
    SELECT part_id, name, 1 AS qty
    FROM parts
    WHERE part_id = 1

    UNION ALL

    SELECT p.part_id, p.name, be.qty * b.quantity
    FROM bom_explode be
    JOIN bom b ON be.part_id = b.parent_id
    JOIN parts p ON b.child_id = p.part_id
)
SELECT * FROM bom_explode;

执行结果：

part_id	name	qty
1	电脑整机	1
2	主板	1
4	内存条	2
3	CPU	1
5	散热器	1

---

五、实战案例：斐波那契数列

用递归 CTE 生成前 10 个斐波那契数：

WITH RECURSIVE fib AS (
    SELECT
        1 AS n,
        0 AS fib_n,
        1 AS fib_next

    UNION ALL

    SELECT
        n + 1,
        fib_next,
        fib_n + fib_next
    FROM fib
    WHERE n < 10
)
SELECT n, fib_n AS fibonacci_number FROM fib;

执行结果：

n	fibonacci_number
1	0
2	1
3	1
4	2
5	3
6	5
7	8
8	13
9	21
10	34

---

六、递归 CTE 的性能与注意事项

1. 必须包含终止条件

递归项中的 WHERE 条件必须能让递归最终停止，否则会死循环。PostgreSQL 默认递归深度限制为 100，可通过设置调整：

-- 修改递归深度限制（单位：迭代次数）
SET recursive_cte_max_depth = 1000;
-- 或全局设置
ALTER DATABASE mydb SET recursive_cte_max_depth = 1000;

2. 递归项中不能使用聚合/窗口函数

不能在递归项中使用 GROUP BY、ORDER BY、DISTINCT、窗口函数等会影响行的操作。

3. 性能优化

• 确保递归连接字段有 索引（如 manager_id、parent_id）
• 避免在递归项中做昂贵的计算
• 只在叶子节点多的场景用递归 CTE，简单层级用 Nested Set 模型更高效

4. 替代方案对比

方案	适用场景	优缺点
递归 CTE	树形查询、BOM、路径查找	灵活、一个 SQL 搞定；大数据量可能较慢
Nested Set	频繁读取的树结构	查询快；维护复杂（增删改要重算左右值）
程序递归	复杂业务逻辑	可控性强；需要额外代码和多次查询

---

七、常见陷阱

⚠️ 坑 1：忘记写 RECURSIVE

-- ❌ 报错：cte_name 引用了自身但未声明 RECURSIVE
WITH cte AS (... UNION ALL SELECT ... FROM cte ...)

⚠️ 坑 2：UNION 误写成 UNION ALL 使用 UNION 会去重，对于树形查询可能导致数据丢失（同名的部门可能被合并）。

⚠️ 坑 3：无限循环导致数据库打满 始终确保递归项有清晰的终止条件，先用小数据集测试。

---

一句话总结

递归 CTE = 非递归项（起点）+ 递归项（迭代）+ 终止条件，专治「查一层写一层」的树形结构难题。

掌握递归 CTE，组织架构、分类树、BOM、图遍历这些曾经需要写代码的场景，一个 SQL 就搞定了。