递归 CTE 组织架构遍历

示例说明

自引用表的遍历是递归 CTE 的本命场景：锚点取顶层（manager 为空），递归臂沿 manager_id 向下 join，每轮产出一层。脏数据里的环（A 管 B 管 A）会让递归无限展开——路径数组进 WHERE 条件把已访问节点排除，环在语义上被截断。ARRAY 的 @> 包含运算符是 PostgreSQL 的数组利器。

技术要点

• 锚点加递归臂的两段式结构
• 路径数组排除已访问节点防环
• 同一模式反向即子树查询

代码解读

正向查询逐层累积 depth 与 chain，report_path 数组既用于排序（同前缀聚一起）又用于防环；chain 的拼接把汇报线变成可读文本。反向查询把锚点换成指定员工、递归臂不变，count 与 max 一行汇总下属总数与层级深度。

使用建议

大组织加 maxdepth 限制或物化中间结果。环数据应触发数据质量告警而非静默截断。路径数组的包含判断在大树上可换成 EXISTS 子查询。

代码示例

-- 递归展开管理链：从 CEO 到每层汇报关系
WITH RECURSIVE org_tree AS (
  SELECT
    employee_id,
    manager_id,
    name,
    1 AS depth,
    ARRAY[employee_id] AS report_path,
    name::text AS chain
  FROM employees
  WHERE manager_id IS NULL  -- 锚点：顶层节点

  UNION ALL

  SELECT
    e.employee_id,
    e.manager_id,
    e.name,
    t.depth + 1,
    t.report_path || e.employee_id,
    t.chain || ' > ' || e.name
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.employee_id
  WHERE NOT t.report_path @> ARRAY[e.employee_id]  -- 防环
)
SELECT
  name,
  depth,
  chain
FROM org_tree
ORDER BY report_path;

-- 反向查询：某人的全部下属（子树）
WITH RECURSIVE subtree AS (
  SELECT employee_id, name, 1 AS depth
  FROM employees WHERE employee_id = 7
  UNION ALL
  SELECT e.employee_id, e.name, s.depth + 1
  FROM employees e JOIN subtree s ON e.manager_id = s.employee_id
)
SELECT count(*) AS total_reports,
       max(depth) AS max_depth
FROM subtree;