中文
devexamples

Recursive CTE Bill of Materials Explosion

Explode product bills of materials with a recursive CTE, multiplying quantities down each assembly path and rolling up costs.

SqlData Integrity

Code

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

Related Examples