递归 CTE

概念定义

有些查询天然是迭代式的：找某装配件下的全部零件、某经理下的全部下属、可达的全部节点。递归 CTE 给了 SQL 这个循环：种子行、扩展规则、以“迭代产出为空”终止。

工作机制

锚部产出种子集；递归项作用于上一轮输出，结果追加进工作表；递归项返回零行时停止。PostgreSQL 暴露搜索路径与环检测；深度跟踪靠在迭代中携带 level 或 path 列。

使用场景

模式中的树遍历、包与物料图的传递依赖、邻接表模型——应用侧递归会按层级往返数据库的场景。读远多于写时，物化路径或嵌套集是替代方案。

常见误区

UNION ALL 只是更快，从不去重：带环的图用它就是死循环，UNION 的去重才是终止保证。递归项每轮重新求值，其连接列必须有索引。极深的链条会撑大工作集；引擎有迭代上限，遍历中途撞上限是运维错误而非查询结果。

代码示例

-- BOM 展开：每层用量沿路径连乘
WITH RECURSIVE explosion AS (
  SELECT
    b.child_part_id,
    b.parent_part_id,
    b.quantity_per,
    b.quantity_per AS total_quantity,
    b.child_part_id::text AS path,
    1 AS level
  FROM bom_edges b
  WHERE b.parent_part_id = 'BIKE-100'  -- 顶层成品

  UNION ALL

  SELECT
    c.child_part_id,
    c.parent_part_id,
    c.quantity_per,
    e.total_quantity * c.quantity_per,
    e.path || ' > ' || c.child_part_id,
    e.level + 1
  FROM bom_edges c
  JOIN explosion e ON c.parent_part_id = e.child_part_id
  WHERE e.level < 10  -- 深度保险丝
)
SELECT
  child_part_id,
  level,
  total_quantity,
  path
FROM explosion
ORDER BY path;

-- 成本汇总：叶子节件数乘单价回卷到成品
WITH costs AS (
  SELECT
    child_part_id,
    SUM(total_quantity) AS qty_needed
  FROM explosion
  GROUP BY child_part_id
)
SELECT
  c.child_part_id,
  c.qty_needed,
  p.unit_cost_cents,
  c.qty_needed * p.unit_cost_cents AS extended_cost
FROM costs c
JOIN parts p ON p.part_id = c.child_part_id
ORDER BY extended_cost DESC;

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