如何基于层级关系实现BOM表的SQL递归查询

针对你的bom表（字段id、skuFather、skuSon、qtys），要获取指定主SKU（比如A00001）下所有层级的子SKU及对应数量，普通的IN子查询只能处理固定层级的关联，没法覆盖多层级的嵌套关系，这里用**递归CTE（公共表表达式）**是最直接的解决方案，具体SQL如下：

WITH RECURSIVE bom_hierarchy AS (
    -- 锚点成员：获取指定主SKU的直接子级
    SELECT 
        skuFather,
        skuSon,
        qtys,
        1 AS level  -- 标记层级，可选，方便看嵌套深度
    FROM bom
    WHERE skuFather = 'A00001'
    
    UNION ALL
    
    -- 递归成员：递归获取下一级子SKU
    SELECT 
        b.skuFather,
        b.skuSon,
        b.qtys,
        bh.level + 1 AS level
    FROM bom b
    JOIN bom_hierarchy bh ON b.skuFather = bh.skuSon
)
-- 查询最终结果，可根据需求调整输出字段
SELECT skuFather, skuSon, qtys, level
FROM bom_hierarchy
ORDER BY level, skuFather, skuSon;

代码说明： #

• 锚点成员：先取出A00001的直接子SKU，作为递归的起始节点
• 递归成员：通过JOIN将上一轮查询到的子SKU作为父SKU，继续查询它的下一级子SKU，直到没有更多子级为止
• level字段可选，用来标记当前子SKU所处的层级，便于区分嵌套关系

注意事项： #

• 如果你的BOM数据存在循环引用（比如SKU1→SKU2→SKU1），递归会陷入死循环，此时可以在递归成员中加条件排除已访问过的SKU，或者数据库支持的话用MAXRECURSION限制递归深度（比如SQL Server）
• 如果需要计算每个子SKU的累计数量（比如多级嵌套的总用量），可以在递归中增加累计字段，示例代码如下：

WITH RECURSIVE bom_hierarchy AS (
    SELECT 
        skuFather,
        skuSon,
        qtys,
        qtys AS total_qtys,  -- 直接子级累计数量等于自身qtys
        1 AS level
    FROM bom
    WHERE skuFather = 'A00001'
    
    UNION ALL
    
    SELECT 
        b.skuFather,
        b.skuSon,
        b.qtys,
        bh.total_qtys * b.qtys AS total_qtys,  -- 累计数量=父级累计×当前qtys
        bh.level + 1 AS level
    FROM bom b
    JOIN bom_hierarchy bh ON b.skuFather = bh.skuSon
)
SELECT skuFather, skuSon, qtys, total_qtys, level
FROM bom_hierarchy
ORDER BY level, skuFather, skuSon;

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