如何用递归CTE生成仅含零件的扁平化采购/制造BOM

要从层级BOM生成仅包含底层零件的扁平化采购/制造BOM，核心是识别并排除中间子装配体——这类条目既是某个父装配体的子件，同时自身又包含下层级零件。以下是两种高效的实现方式：

方法1：递归后过滤父装配体条目 #

先通过递归CTE获取所有层级的BOM条目，再排除那些在BOMLEGER表中作为父节点（PARENT）存在的零件：

WITH RecursiveBOM AS (
    -- 锚点：指定顶层装配体，替换为实际的顶层编号
    SELECT 
        PARENT,
        CHILD AS PartNumber,
        1 AS Level
    FROM BOMLEGER
    WHERE PARENT = 'ASSY-0000001'
    UNION ALL
    -- 递归遍历所有子层级
    SELECT 
        rb.PARENT,
        bl.CHILD AS PartNumber,
        rb.Level + 1 AS Level
    FROM RecursiveBOM rb
    JOIN BOMLEGER bl ON rb.PartNumber = bl.PARENT
)
-- 过滤掉所有作为父装配体的零件，仅保留底层零件
SELECT DISTINCT PartNumber
FROM RecursiveBOM
WHERE PartNumber NOT IN (SELECT PARENT FROM BOMLEGER);

方法2：递归过程中标记叶子节点 #

在递归CTE内部直接判断当前零件是否为叶子节点（即没有子零件），最后只筛选叶子节点：

WITH RecursiveBOM AS (
    SELECT 
        PARENT,
        CHILD AS PartNumber,
        1 AS Level,
        -- 标记是否为叶子节点：无后续子零件则为1
        CASE WHEN NOT EXISTS (SELECT 1 FROM BOMLEGER bl2 WHERE bl2.PARENT = bl.CHILD)
             THEN 1
             ELSE 0
        END AS IsLeaf
    FROM BOMLEGER bl
    WHERE PARENT = 'ASSY-0000001'
    UNION ALL
    SELECT 
        rb.PARENT,
        bl.CHILD AS PartNumber,
        rb.Level + 1 AS Level,
        CASE WHEN NOT EXISTS (SELECT 1 FROM BOMLEGER bl2 WHERE bl2.PARENT = bl.CHILD)
             THEN 1
             ELSE 0
        END AS IsLeaf
    FROM RecursiveBOM rb
    JOIN BOMLEGER bl ON rb.PartNumber = bl.PARENT
)
-- 仅保留叶子节点的底层零件
SELECT DISTINCT PartNumber
FROM RecursiveBOM
WHERE IsLeaf = 1;

优化方案：利用PARTMASTER的类型字段 #

如果PARTMASTER表中存在区分零件/装配体的字段（比如IsAssembly，1表示装配体，0表示零件），直接用该字段过滤会更高效：

WITH RecursiveBOM AS (
    SELECT 
        PARENT,
        CHILD AS PartNumber,
        1 AS Level
    FROM BOMLEGER
    WHERE PARENT = 'ASSY-0000001'
    UNION ALL
    SELECT 
        rb.PARENT,
        bl.CHILD AS PartNumber,
        rb.Level + 1 AS Level
    FROM RecursiveBOM rb
    JOIN BOMLEGER bl ON rb.PartNumber = bl.PARENT
)
SELECT DISTINCT rb.PartNumber
FROM RecursiveBOM rb
JOIN PARTMASTER pm ON rb.PartNumber = pm.PartNumber
WHERE pm.IsAssembly = 0;

内容的提问来源于stack exchange，提问作者JMPWalker