PostgreSQL / 系统课时
递归查询
递归查询
递归 CTE 适合处理“从一个节点不断找到下一层”的数据,例如组织树、菜单目录和路径展开。本篇用一棵员工汇报树演示锚点查询、递归项、工作表迭代与防环技巧,最终得到每个节点的层级和完整路径。
学习目标
- 写出由锚点项、
UNION ALL和递归项组成的递归 CTE - 理解 PostgreSQL 如何逐轮扩展工作结果直到没有新行
- 用路径数组限制循环,并输出层级与完整访问路径
递归 CTE 的最小结构
WITH RECURSIVE 中,锚点项先产生起始行;递归项读取上一轮结果,再连接出下一层。两部分通过 UNION ALL 合并。虽然语法写成递归,PostgreSQL 内部实际按工作表一轮轮迭代,直到递归项不再产生新行。

展开组织树并记录路径
下面把员工数据放进普通 CTE,再从 CEO 开始递归。path_ids 同时承担排序依据和防环记录:只有当前员工编号尚未出现在路径中时,才允许继续向下扩展。
WITH RECURSIVE employees(id, name, manager_id) AS (
VALUES
(1, 'CEO', NULL::integer),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, 'Dev1', 2),
(5, 'Dev2', 2),
(6, 'Accountant', 3)
),
org AS (
SELECT
e.id,
e.name,
e.manager_id,
0 AS depth,
ARRAY[e.id] AS path_ids,
e.name::text AS path_text
FROM employees AS e
WHERE e.manager_id IS NULL
UNION ALL
SELECT
e.id,
e.name,
e.manager_id,
o.depth + 1,
o.path_ids || e.id,
o.path_text || ' > ' || e.name
FROM org AS o
JOIN employees AS e
ON e.manager_id = o.id
WHERE NOT (e.id = ANY(o.path_ids))
)
SELECT id, name, depth, path_text
FROM org
ORDER BY path_ids;预期结果
id | name | depth | path_text
----+------------+-------+------------------------
1 | CEO | 0 | CEO
2 | CTO | 1 | CEO > CTO
4 | Dev1 | 2 | CEO > CTO > Dev1
5 | Dev2 | 2 | CEO > CTO > Dev2
3 | CFO | 1 | CEO > CFO
6 | Accountant | 2 | CEO > CFO > Accountant终止条件与防环决定安全性
树结构通常会自然终止,因为叶子节点没有子节点;图结构则可能存在 A 指向 B、B 又回到 A 的环。若没有终止条件,递归项会持续生成行。路径数组让每一条遍历分支记住已经访问的节点,ANY(path_ids) 检查能阻止同一路径再次进入旧节点。
UNION 也能去掉完全重复的结果,但它既增加去重成本,也不能替代业务上的环检测:只要行中还包含深度、路径等变化字段,同一节点仍可能以不同记录继续出现。处理图遍历时,应明确“节点是否允许重复访问”和“最大深度是否需要限制”。
控制遍历范围与输出顺序
递归查询最容易失控的地方不是语法,而是起点过宽、连接条件不精确和递归深度无界。生产查询应尽量从明确根节点开始,并为递归连接列建立合适索引,例如组织树通常需要索引 manager_id。
输出顺序与内部计算顺序是两件事。示例用 path_ids 排序得到接近深度优先的树形阅读顺序,但不要依赖递归产生行的物理顺序。只要把起点、递归连接、终止条件和去环规则写清楚,递归 CTE 就能把层级结构转换成普通关系结果,继续参与过滤、聚合和关联。