PHP小码农

Appearance

递归 CTE（WITH RECURSIVE） ​

本节讲解递归 CTE（Recursive CTE）的语法结构、锚点（Anchor）与递归项（Recursive Term）的分工、终止条件与循环防护、UNION 与 UNION ALL 在递归中的去重作用，以及用它遍历树形结构与生成序列的实战方法。学完本节，你将能安全地写出不会死循环的递归查询。

前置知识

阅读本节前，建议先了解：公共表表达式基础（CTE）

基础概念 ​

递归 CTE（Recursive CTE） 指 CTE 引用自身的查询，用 WITH RECURSIVE 声明。它由两部分组成，用 UNION [ALL] 连接。

概念术语说明
递归 CTERecursive CTE引用自身的公共表表达式
锚点Anchor / Non-Recursive Term不引用自身的起始查询
递归项Recursive Term引用 CTE 自身的查询
工作表Working Table存放当前迭代产生的新行
结果表Result Table累积所有已产出的行
迭代Iteration一次递归展开
终止条件Termination Condition递归项返回 0 行时结束
层级列Depth Column手工维护的递归深度计数

递归 CTE 的执行阶段：

阶段动作用到哪张表
1. 初始化执行锚点，结果写入结果表与工作表结果表 / 工作表
2. 迭代用工作表的行执行递归项工作表 → 新行
3. 累积新行加入结果表，替换工作表结果表 / 工作表
4. 判断若新行为 0 则结束，否则回到阶段 2—
5. 输出主查询读取结果表结果表

UNION 与 UNION ALL 在递归中的区别：

写法去重范围是否可能死循环适用场景
UNION对所有已产出的行去重图有环时会自动停止图遍历、不确定有无环
UNION ALL不去重有环时会无限循环树结构、序列生成

核心直觉：WITH RECURSIVE 不是「自动迭代到收敛」，而是「反复执行递归项，直到它不再产生新行」。终止靠的是递归项自己的 WHERE 条件。

语法与用法 ​

text
WITH RECURSIVE cte_名称 AS (
  -- 锚点：不引用 cte_名称
  初始查询
  UNION [ALL]
  -- 递归项：FROM 中引用 cte_名称
  引用 cte_名称 的查询
)
SELECT ... FROM cte_名称 ...;

-- 手工维护层级
WITH RECURSIVE t AS (
  初始查询, 0 AS depth
  UNION ALL
  SELECT ..., t.depth + 1 FROM t WHERE t.depth < 10
)
SELECT * FROM t;

-- 防止无限递归：加深度上限或路径去重
WITH RECURSIVE t AS (
  初始查询, ARRAY[起始键] AS path
  UNION ALL
  SELECT ..., t.path || 下一个键
  FROM t
  WHERE NOT (下一个键 = ANY(t.path))   -- 环检测
)
SELECT * FROM t;

准备本节贯穿使用的示例表：

sql
-- 员工表：带自关联的上级字段，构成一棵组织树
CREATE TABLE employees (
  id         INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL,
  manager_id INTEGER REFERENCES employees (id),
  dept       TEXT    NOT NULL,
  salary     NUMERIC(10, 2) NOT NULL
);

-- 分类表：商品类目树
CREATE TABLE categories (
  id        INTEGER PRIMARY KEY,
  parent_id INTEGER,
  name      TEXT NOT NULL
);

-- 订单明细：用于生成日期序列演示
CREATE TABLE order_days (
  day   DATE PRIMARY KEY,
  cnt   INTEGER NOT NULL DEFAULT 0
);

造数：

sql
-- 组织树：1 名 CEO，4 名总监，16 名经理，64 名员工
INSERT INTO employees (id, name, manager_id, dept, salary) VALUES
  (1,  '张总', NULL, '总部', 50000);

INSERT INTO employees (id, name, manager_id, dept, salary)
SELECT 1 + g, '总监' || g, 1, (ARRAY['技术','销售','市场','运营'])[1 + g], 30000
FROM generate_series(1, 4) AS g;

INSERT INTO employees (id, name, manager_id, dept, salary)
SELECT 5 + g, '经理' || g, 2 + (g % 4), (ARRAY['技术','销售','市场','运营'])[1 + g % 4], 18000
FROM generate_series(1, 16) AS g;

INSERT INTO employees (id, name, manager_id, dept, salary)
SELECT 21 + g, '员工' || g, 6 + (g % 16), (ARRAY['技术','销售','市场','运营'])[1 + g % 4], 9000
FROM generate_series(1, 64) AS g;

-- 类目树：3 层
INSERT INTO categories (id, parent_id, name) VALUES
  (1, NULL, '全部商品'),
  (2, 1, '数码'), (3, 1, '服饰'), (4, 1, '家居'),
  (5, 2, '手机'), (6, 2, '电脑'), (7, 3, '男装'), (8, 3, '女装'),
  (9, 5, '智能手机'), (10, 5, '老人机'), (11, 6, '笔记本'), (12, 6, '台式机');

INSERT INTO order_days (day, cnt)
SELECT d::date, (random() * 100)::integer
FROM generate_series(DATE '2025-01-01', DATE '2025-01-10', '1 day') AS d
ON CONFLICT DO NOTHING;

详细说明 ​

自顶向下遍历组织树 ​

用递归 CTE 从 CEO 出发，逐层展开所有下属：

sql
WITH RECURSIVE org AS (
  -- 锚点：从根节点 CEO 开始
  SELECT id, name, manager_id, dept, salary, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- 递归项：找出「上级在上一轮结果中」的员工
  SELECT e.id, e.name, e.manager_id, e.dept, e.salary, o.level + 1
  FROM employees e
  JOIN org o ON o.id = e.manager_id
)
SELECT level, count(*) AS cnt, sum(salary) AS total_salary
FROM org
GROUP BY level
ORDER BY level;
text
 level | cnt | total_salary
-------+-----+--------------
     1 |   1 |     50000.00
     2 |   4 |    120000.00
     3 |  16 |    288000.00
     4 |  64 |    576000.00
(4 rows)

读法：锚点产出 CEO（level = 1）；第一次迭代用 CEO 的 id 去匹配 manager_id，产出 4 名总监（level = 2）；第二次迭代用 4 名总监的 id 匹配，产出 16 名经理；第三次产出 64 名员工；第四次递归项返回 0 行，递归结束。

关键点：递归项里的 org 代表的是「上一轮新产生的行」，不是累积结果。 这是理解递归 CTE 的核心。

用层级列做缩进展示 ​

level 列是手工维护的，可以在主查询里转成树形缩进：

sql
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, dept, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, e.dept, o.level + 1
  FROM employees e JOIN org o ON o.id = e.manager_id
)
SELECT repeat('    ', level - 1) || name AS 组织架构,
       dept,
       level
FROM org
ORDER BY level, id
LIMIT 12;
text
     组织架构      |  dept  | level
------------------+--------+-------
 张总             | 总部   |     1
     总监1        | 技术   |     2
     总监2        | 销售   |     2
     总监3        | 市场   |     2
     总监4        | 运营   |     2
         经理1    | 技术   |     3
         经理5    | 技术   |     3
         经理9    | 技术   |     3
         经理13   | 技术   |     3
         经理2    | 销售   |     3
         经理6    | 销售   |     3
         经理10   | 销售   |     3
(12 rows)

自底向上遍历（求某人的完整上级链） ​

把锚点换成一个具体的员工，递归项改成「向上找直接上级」：

sql
WITH RECURSIVE chain AS (
  -- 锚点：从指定员工出发
  SELECT id, name, manager_id, 0 AS distance
  FROM employees
  WHERE id = 50
  UNION ALL
  -- 递归项：找出「上一轮的 manager_id 对应的员工」
  SELECT e.id, e.name, e.manager_id, c.distance + 1
  FROM employees e
  JOIN chain c ON e.id = c.manager_id
)
SELECT distance, id, name, manager_id
FROM chain
ORDER BY distance;
text
 distance | id |  name  | manager_id
----------+----+--------+------------
        0 | 50 | 员工30 |         11
        1 | 11 | 经理7  |          4
        2 |  4 | 总监3  |          1
        3 |  1 | 张总   |           （null）
(4 rows)

方向由连接条件决定：JOIN org o ON o.id = e.manager_id 是向下（org 提供上级），JOIN chain c ON e.id = c.manager_id 是向上（chain 提供下级）。

递归中的环与防护 ​

如果数据中存在环（例如 A 的上级是 B，B 的上级又是 A），UNION ALL 会无限循环，直到内存耗尽或达到 max_stack_depth：

sql
-- 制造一个环
UPDATE employees SET manager_id = 30 WHERE id = 1;

-- 危险！使用 UNION ALL 会无限递归
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e JOIN org o ON o.id = e.manager_id
)
SELECT max(level) FROM org LIMIT 1;
text
ERROR:  stack depth limit exceeded

三种防护写法：

sql
-- 防护 1：改用 UNION 自动去重（对已产出的行去重）
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
  UNION
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e JOIN org o ON o.id = e.manager_id
)
SELECT max(level) FROM org;
sql
-- 防护 2：维护路径数组，遇到重复键就停（推荐，性能可控）
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level, ARRAY[id] AS path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.level + 1, o.path || e.id
  FROM employees e JOIN org o ON o.id = e.manager_id
  WHERE NOT (e.id = ANY(o.path))
)
SELECT max(level) FROM org;
sql
-- 防护 3：硬性深度上限
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e JOIN org o ON o.id = e.manager_id
  WHERE o.level < 20
)
SELECT max(level) FROM org;
防护方式代价是否检测环推荐度
UNION需维护全部已见行✅ 是⭐⭐ 适合中小结果集
路径数组每行存一份路径✅ 是⭐⭐⭐ 推荐
深度上限无额外代价❌ 否（只是截断）⭐⭐ 作为兜底
无防护无❌❌ 禁止

UNION 去重的代价：它需要为所有已产出的行维护哈希表。当结果集很大时，内存占用会显著高于路径数组方案。

递归 CTE 生成序列与日期 ​

递归 CTE 是生成序列的通用工具，尤其在需要「以现有数据的边界为起点」时：

sql
-- 生成 1~10 的整数序列
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 10
)
SELECT n FROM seq;
text
 n
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)
sql
-- 补齐缺失日期：从订单表的最小最大日期生成完整日历
WITH RECURSIVE calendar AS (
  SELECT min(day) AS d FROM order_days
  UNION ALL
  SELECT (d + 1) FROM calendar WHERE d < (SELECT max(day) FROM order_days)
)
SELECT c.d, coalesce(o.cnt, 0) AS cnt
FROM calendar c
LEFT JOIN order_days o ON o.day = c.d
ORDER BY c.d;
text
     d      | cnt
------------+-----
 2025-01-01 |  73
 2025-01-02 |  12
 2025-01-03 |  89
 2025-01-04 |  41
 2025-01-05 |   5
 2025-01-06 |  66
 2025-01-07 |  27
 2025-01-08 |  98
 2025-01-09 |  34
 2025-01-10 |  52
(10 rows)

注意：纯序列生成用 generate_series() 更高效。递归 CTE 的价值在于「边界来自另一张表」的场景。

递归展开类目树并统计叶子 ​

递归 CTE 可以同时完成「展开」与「打标记」，用于计算叶子节点：

sql
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS level, ARRAY[id] AS path
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.level + 1, t.path || c.id
  FROM categories c JOIN tree t ON t.id = c.parent_id
)
SELECT
  id,
  repeat('  ', level - 1) || name AS 类目,
  level,
  path,
  NOT EXISTS (SELECT 1 FROM categories c2 WHERE c2.parent_id = tree.id) AS is_leaf
FROM tree
ORDER BY path;
text
 id |        类目        | level |       path        | is_leaf
----+--------------------+-------+-------------------+---------
  1 | 全部商品           |     1 | {1}               | f
  2 |   数码             |     2 | {1,2}             | f
  5 |     手机           |     3 | {1,2,5}           | f
  9 |       智能手机     |     4 | {1,2,5,9}         | t
 10 |       老人机       |     4 | {1,2,5,10}        | t
  6 |     电脑           |     3 | {1,2,6}           | f
 11 |       笔记本       |     4 | {1,2,6,11}        | t
 12 |       台式机       |     4 | {1,2,6,12}        | t
  3 |   服饰             |     2 | {1,3}             | f
  7 |     男装           |     3 | {1,3,7}           | t
  8 |     女装           |     3 | {1,3,8}           | t
  4 |   家居             |     2 | {1,4}             | t
(12 rows)

ORDER BY path 利用了数组的字典序比较，天然实现深度优先的排序——这比自己拼字符串更可靠。

实战示例 ​

目标：计算每个部门的「含各级下属的薪资总额」与「组织层级深度」，并找出汇报链最长的员工。

步骤 1：展开完整组织树

sql
CREATE TABLE org_tree AS
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, dept, salary, 1 AS level, ARRAY[id] AS path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, e.dept, e.salary, o.level + 1, o.path || e.id
  FROM employees e JOIN org o ON o.id = e.manager_id
  WHERE NOT (e.id = ANY(o.path))
)
SELECT * FROM org;

CREATE INDEX ON org_tree (path);
ANALYZE org_tree;

SELECT count(*) AS total_nodes, max(level) AS max_level FROM org_tree;
text
 total_nodes | max_level
-------------+-----------
          85 |         4
(1 row)

步骤 2：按部门统计各级人数

sql
SELECT dept,
       count(*) FILTER (WHERE level = 1) AS lv1,
       count(*) FILTER (WHERE level = 2) AS lv2,
       count(*) FILTER (WHERE level = 3) AS lv3,
       count(*) FILTER (WHERE level = 4) AS lv4,
       count(*)                          AS total
FROM org_tree
GROUP BY dept
ORDER BY dept;
text
  dept  | lv1 | lv2 | lv3 | lv4 | total
--------+-----+-----+-----+-----+-------
 总部   |   1 |   0 |   0 |   0 |     1
 技术   |   0 |   1 |   4 |  16 |    21
 市场   |   0 |   1 |   4 |  16 |    21
 销售   |   0 |   1 |   4 |  16 |    21
 运营   |   0 |   1 |   4 |  16 |    21
(5 rows)

步骤 3：计算每个经理的子树人数与薪资池（自底向上聚合）

sql
SELECT
  o.manager_id,
  m.name AS manager_name,
  count(*)         AS subtree_size,
  sum(o.salary)    AS subtree_salary
FROM org_tree o
JOIN employees m ON m.id = o.manager_id
GROUP BY o.manager_id, m.name
ORDER BY subtree_size DESC
LIMIT 10;
text
 manager_id | manager_name | subtree_size | subtree_salary
------------+--------------+--------------+----------------
          1 | 张总         |           84 |      984000.00
          2 | 总监1        |           20 |      234000.00
          3 | 总监2        |           20 |      234000.00
          4 | 总监3        |           20 |      234000.00
          5 | 总监4        |           20 |      234000.00
          6 | 经理1        |            4 |       45000.00
          7 | 经理2        |            4 |       45000.00
          8 | 经理3        |            4 |       45000.00
          9 | 经理4        |            4 |       45000.00
         10 | 经理5        |            4 |       45000.00
(10 rows)

步骤 4：用路径找出「某人所属的完整上级链」

sql
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, ARRAY[name] AS chain_path
  FROM employees WHERE id = 84
  UNION ALL
  SELECT e.id, e.name, e.manager_id, chain_path || e.name
  FROM employees e JOIN chain c ON e.id = c.manager_id
)
SELECT id, name, chain_path
FROM chain
ORDER BY array_length(chain_path, 1) DESC
LIMIT 1;
text
 id |  name  |                chain_path
----+--------+-------------------------------------------
  1 | 张总   | {员工64,经理12,总监4,张总}
(1 row)

步骤 5：找出汇报链最长的员工（自底向上求深度）

sql
WITH RECURSIVE up AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE id NOT IN (SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL)
  UNION ALL
  SELECT e.id, e.name, e.manager_id, u.depth + 1
  FROM employees e JOIN up u ON e.id = u.manager_id
)
SELECT id, name, depth
FROM up
ORDER BY depth DESC, id
LIMIT 5;
text
 id |  name  | depth
----+--------+-------
  4 | 总监3  |     3
  3 | 总监2  |     3
  5 | 总监4  |     3
  2 | 总监1  |     3
 22 | 员工2  |     1
(5 rows)

步骤 6：用递归 CTE 做时间轴对齐（补齐缺失日期）

sql
WITH RECURSIVE calendar AS (
  SELECT DATE '2025-01-01' AS d
  UNION ALL
  SELECT d + 1 FROM calendar WHERE d < DATE '2025-01-15'
)
SELECT c.d,
       coalesce(o.cnt, 0) AS cnt,
       CASE WHEN o.day IS NULL THEN '缺失' ELSE '有数据' END AS status
FROM calendar c
LEFT JOIN order_days o ON o.day = c.d
ORDER BY c.d;
text
     d      | cnt | status
------------+-----+--------
 2025-01-01 |  73 | 有数据
 2025-01-02 |  12 | 有数据
 ...
 2025-01-10 |  52 | 有数据
 2025-01-11 |   0 | 缺失
 2025-01-12 |   0 | 缺失
 2025-01-13 |   0 | 缺失
 2025-01-14 |   0 | 缺失
 2025-01-15 |   0 | 缺失
(15 rows)

步骤 7：方案对比总结

需求推荐写法递归方向
展开完整组织树递归 CTE + 路径数组自顶向下
求某人的上级链递归 CTE自底向上
生成 1~N 序列generate_series()无需递归
补齐缺失日期（边界来自数据）递归 CTE自增
统计子树规模递归 CTE 结果表 + GROUP BY先展开后聚合
图的最短路径递归 CTE + 路径去重广度优先

注意事项 ​

  1. 必须有终止条件，否则会耗尽资源。 递归项里的 WHERE 是唯一的刹车：
sql
-- 错误！没有终止条件的自增
WITH RECURSIVE t AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM t)
SELECT * FROM t LIMIT 10;
text
ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth".
  1. 数据有环时必须防护。 树结构被错误更新成有环时，UNION ALL 会死循环：
sql
-- 正确：路径去重
WHERE NOT (e.id = ANY(o.path))
  1. 递归项只能引用 CTE 一次，且不能是外连接的内侧：
sql
-- 错误！递归引用出现在 LEFT JOIN 的右侧
WITH RECURSIVE t AS (
  SELECT 1 AS n
  UNION ALL
  SELECT a.n FROM other a LEFT JOIN t ON t.n = a.n
)
SELECT * FROM t;
text
ERROR:  recursive reference to query "t" must not appear within an outer join
  1. 递归项中不能使用聚合、DISTINCT、GROUP BY、窗口函数：
sql
-- 错误！递归项里用了聚合
WITH RECURSIVE t AS (
  SELECT 1 AS n
  UNION ALL
  SELECT max(n) + 1 FROM t
)
SELECT * FROM t;
text
ERROR:  aggregate functions are not allowed in a recursive query's recursive term

  1. UNION 而非 UNION ALL 会附带去重开销。 只在确实可能有环或重复路径时使用。

  2. 递归 CTE 与 SEARCH / CYCLE 子句：SQL 标准提供了 SEARCH DEPTH FIRST / CYCLE 语法，PostgreSQL 18 尚未支持，需要手工维护路径与层级列。

  3. LIMIT 不会阻止递归一直跑到终止，它只在最后截取输出：

sql
-- 递归仍然会产生全部 100 万行，只是最后返回 10 行
WITH RECURSIVE t AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM t WHERE n < 1000000)
SELECT * FROM t LIMIT 10;

最佳实践 ​

  1. 先写锚点，再写递归项，最后加终止条件。 三步走能显著降低出错率。

  2. 默认使用 UNION ALL 并手工维护 level 与 path 列，既快又可控：

sql
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level, ARRAY[id] AS path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.level + 1, o.path || e.id
  FROM employees e JOIN org o ON o.id = e.manager_id
  WHERE NOT (e.id = ANY(o.path))
)
SELECT * FROM org;
  1. 用路径数组的字典序排序实现深度优先遍历，比事后拼接字符串更可靠：
sql
ORDER BY path
  1. 为递归的连接键建索引，让每一轮迭代都能走索引：
sql
CREATE INDEX idx_employees_manager ON employees (manager_id);
CREATE INDEX idx_categories_parent ON categories (parent_id);
  1. 把递归结果先物化成表再聚合，避免对递归输出重复计算：
sql
CREATE TABLE org_tree AS
WITH RECURSIVE org AS (...) SELECT * FROM org;
CREATE INDEX ON org_tree (manager_id);
ANALYZE org_tree;
  1. 纯序列生成优先用 generate_series()，只有边界来自另一张表时才用递归：
sql
-- 更好
SELECT d::date FROM generate_series(DATE '2025-01-01', DATE '2025-01-10', '1 day') AS d;
  1. 务必设置深度上限作为兜底，即使已经有了环检测：
sql
WHERE o.level < 50

下一节

继续学习：CTE 物化与优化

参考链接 ​