PostgreSQL / 系统课时

递归查询

递归查询

递归 CTE 适合处理“从一个节点不断找到下一层”的数据,例如组织树、菜单目录和路径展开。本篇用一棵员工汇报树演示锚点查询、递归项、工作表迭代与防环技巧,最终得到每个节点的层级和完整路径。

学习目标

  • 写出由锚点项、UNION ALL 和递归项组成的递归 CTE
  • 理解 PostgreSQL 如何逐轮扩展工作结果直到没有新行
  • 用路径数组限制循环,并输出层级与完整访问路径

递归 CTE 的最小结构

WITH RECURSIVE 中,锚点项先产生起始行;递归项读取上一轮结果,再连接出下一层。两部分通过 UNION ALL 合并。虽然语法写成递归,PostgreSQL 内部实际按工作表一轮轮迭代,直到递归项不再产生新行。

递归 CTE 的逐轮扩展过程

展开组织树并记录路径

下面把员工数据放进普通 CTE,再从 CEO 开始递归。path_ids 同时承担排序依据和防环记录:只有当前员工编号尚未出现在路径中时,才允许继续向下扩展。

sql
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;

预期结果

text
 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 就能把层级结构转换成普通关系结果,继续参与过滤、聚合和关联。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

递归查询

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通