递归 CTE 物料清单展开

示例说明

一辆自行车两个轮子、每个轮子三十二根辐条——总需求不是逐层相加而是沿路径连乘。递归臂里 total_quantity 乘以当前边的单位用量，路径上的乘积累积到叶子就是成品对它的直接需求。depth 保险丝限制十层，配置错误的循环结构会止步而非打挂查询。

技术要点

• 用量沿装配路径连乘
• level 上限充当递归保险丝
• 汇总后按单价回卷成本

代码解读

explosion 从成品出发逐层展开：每行的 total_quantity 已含全部祖先链的用量；path 记录装配路径便于追溯某个零件装在哪条支线上。成本查询把同一零件在不同路径的数量 SUM 到一起——两个轮子的辐条合并成六十四根——再乘单价得到扩展成本排序。

使用建议

公用子件的成本分摊策略要与财务口径一致。BOM 变更历史要留版本快照供追溯展开。超深层次的大 BOM 考虑物化展开结果定期刷新。

代码示例

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