中文
devexamples

Recursive CTE Org Chart Hierarchy

Walk employee hierarchies with a recursive CTE, tracking depth and reporting paths while avoiding cycles in malformed data.

SqlData Integrity

Code

snippet
-- 递归展开管理链：从 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;

Related Examples