SQL 递归 CTE 展开物料清单

功能说明

示例假定 parts 表保存零件号、名称与单位成本，bom 表保存父子关系与用量。递归 CTE 从装配体 ASM-100 的直接子件开始，每层把父级用量乘以子件用量，并把经过的零件号追加到 path。最终查询连接 parts 得到缩进名称、累计用量和扩展成本，再用窗口函数只汇总叶子节点，得到整个装配体的物料总成本 1350 分。

关键代码

递归部分由 UNION ALL 连接基例与递归项，递归项通过 JOIN bom ON parent_part_no = 当前 part_no 向下展开。qty 沿路径相乘，因此共享零件在不同分支中会得到各自正确的累计用量。NOT (child = ANY(path)) 阻止再次进入已访问节点；depth < 10 是额外的防御边界。叶子判定使用 EXISTS 子查询，窗口 SUM 按 root_part_no 分区，把叶子成本重复显示在每一行。

使用注意

BOM 数据可能包含环，数据库不会自动阻止，必须像示例一样做路径检查或深度限制。这里把装配体自身单位成本设为 0，只汇总叶子；如果装配还有人工或装配费用，需要单独加上，不能重复计算子件。共享子装配在多个分支出现时会按路径重复计数，这正是按用量展开的预期语义，但与按零件去重的统计不同。递归深度和结果行数都可能迅速膨胀。

验证方式

运行查询后，ASM-100 下应出现 SUB-200、SUB-300、BOLT-10 与 PLATE-1 的层级行，BOLT-10 在两条路径上的累计用量分别为 8 和 6，PLATE-1 为 2。每行的 rolled_up_leaf_cost_cents 都应为 1350。可临时插入一条指回父级的边，确认路径保护使查询仍能结束。

代码示例

-- Schema assumptions (PostgreSQL 15+):
--   parts(part_no text PRIMARY KEY, name text NOT NULL, unit_cost_cents integer NOT NULL)
--   bom(parent_part_no text REFERENCES parts, child_part_no text REFERENCES parts,
--       qty integer NOT NULL CHECK (qty > 0), PRIMARY KEY (parent_part_no, child_part_no))
-- The TEMP tables mirror those definitions for a scratch run.

CREATE TEMP TABLE parts (
    part_no         text PRIMARY KEY,
    name            text NOT NULL,
    unit_cost_cents integer NOT NULL CHECK (unit_cost_cents >= 0)
);

CREATE TEMP TABLE bom (
    parent_part_no text NOT NULL REFERENCES parts(part_no),
    child_part_no  text NOT NULL REFERENCES parts(part_no),
    qty            integer NOT NULL CHECK (qty > 0),
    PRIMARY KEY (parent_part_no, child_part_no)
);

INSERT INTO parts (part_no, name, unit_cost_cents) VALUES
    ('ASM-100', 'Desk frame assembly', 0),
    ('SUB-200', 'Left leg kit', 0),
    ('SUB-300', 'Right leg kit', 0),
    ('BOLT-10', 'M6 bolt', 25),
    ('PLATE-1', 'Steel plate', 500);

INSERT INTO bom (parent_part_no, child_part_no, qty) VALUES
    ('ASM-100', 'SUB-200', 2),
    ('ASM-100', 'SUB-300', 1),
    ('SUB-200', 'BOLT-10', 4),
    ('SUB-200', 'PLATE-1', 1),
    ('SUB-300', 'BOLT-10', 6);

WITH RECURSIVE exploded AS (
    SELECT
        b.parent_part_no AS root_part_no,
        b.child_part_no  AS part_no,
        1                AS depth,
        b.qty            AS qty,
        ARRAY[b.parent_part_no, b.child_part_no]::text[] AS path
    FROM bom b
    WHERE b.parent_part_no = 'ASM-100'

    UNION ALL

    SELECT
        e.root_part_no,
        b.child_part_no,
        e.depth + 1,
        e.qty * b.qty,
        e.path || b.child_part_no
    FROM exploded e
    JOIN bom b ON b.parent_part_no = e.part_no
    WHERE e.depth < 10
      AND NOT (b.child_part_no = ANY (e.path))
)
SELECT
    e.root_part_no,
    REPEAT('  ', e.depth) || p.name AS indented_name,
    e.part_no,
    e.qty,
    p.unit_cost_cents,
    e.qty * p.unit_cost_cents AS extended_cost_cents,
    SUM(
        CASE
            WHEN EXISTS (SELECT 1 FROM bom child WHERE child.parent_part_no = e.part_no)
                THEN 0
            ELSE e.qty * p.unit_cost_cents
        END
    ) OVER (PARTITION BY e.root_part_no) AS rolled_up_leaf_cost_cents
FROM exploded e
JOIN parts p ON p.part_no = e.part_no
ORDER BY e.path;