Microsoft SQL：如何查询物料清单（BOM）中的最底层部件

给定经典BOM表（结构包含PartId、SubPartId、Quantity），数据如下：

需求是：指定PartId后，仅返回最底层部件（即SubPartId未出现在PartId列中的记录，这类部件无下级子物料）。例如指定PartId=1时，期望返回3、5、8。

原递归查询的问题 #

你提供的递归代码存在两个核心问题：

1. 关联条件错误：JOIN BOM B on B.SubPartId = components.SubPartId无法正确递归获取下级子部件，应该关联父节点的SubPartId与子节点的PartId；
2. 未做底层部件筛选：没有过滤掉仍有下级的部件（比如SubPartId=2，因为它在PartId列中存在，说明有下级）。

正确查询方法 #

方法一：递归获取所有子部件后筛选底层

先通过递归获取指定PartId下的所有层级子部件，再筛选出从未作为父部件（PartId）存在的记录：

WITH BOM AS (
    -- 初始：获取指定PartId的直接子部件
    SELECT SubPartId
    FROM Parts
    WHERE PartId = 1
    UNION ALL
    -- 递归：获取所有下级子部件
    SELECT p.SubPartId
    FROM Parts p
    JOIN BOM b ON b.SubPartId = p.PartId
)
-- 筛选最底层部件：SubPartId未出现在PartId列中
SELECT DISTINCT SubPartId AS 底层部件
FROM BOM
WHERE SubPartId NOT IN (SELECT PartId FROM Parts);

方法二：递归过程中标记底层部件

在递归时直接判断当前SubPartId是否有下级，最后筛选标记为底层的记录：

WITH BOM AS (
    SELECT 
        SubPartId,
        -- 判断当前子部件是否为底层：无对应的PartId则标记为1
        CASE WHEN NOT EXISTS (SELECT 1 FROM Parts p WHERE p.PartId = parts.SubPartId) 
             THEN 1 ELSE 0 END AS IsLeaf
    FROM Parts parts
    WHERE PartId = 1
    UNION ALL
    SELECT 
        p.SubPartId,
        CASE WHEN NOT EXISTS (SELECT 1 FROM Parts p2 WHERE p2.PartId = p.SubPartId) 
             THEN 1 ELSE 0 END AS IsLeaf
    FROM Parts p
    JOIN BOM b ON b.SubPartId = p.PartId
)
SELECT DISTINCT SubPartId AS 底层部件
FROM BOM
WHERE IsLeaf = 1;

两种方法都能得到指定PartId=1时的结果：3、5、8。

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