如何使用SQL递归CTE计算物料清单(BOM)子项累计需求量

问题描述 #

需要基于数据库物料清单（BOM）表和父项需求数量，统计每个子项的总需求量。当前递归CTE仅能正确计算第一层子项的扩展需求量，后续层级计算错误——错误地直接用单位用量乘以顶层父项的需求乘数，而非乘以其父项的扩展需求量。

现有代码 #

declare @multiplier int
set @multiplier = '8';

WITH CTE (ProdBOMNo, ItemDescription, ItemNo, QtyPer, ExtendedQty, PBLevel)
     AS
(   
    SELECT BL.[ProdBOMNo], BL.[Descript], BL.[ItemNo], BL.[QtyPer], BL.[QtyPer] * @multiplier, 1 AS LVL
    FROM [dbName$BOMLine] BL
    WHERE BL.[ProdBOMNo] = '008722'

    UNION ALL

    SELECT BL2.[ProdBOMNo], BL2.[Descript], BL2.[ItemNo], BL2.[QtyPer], BL2.[QtyPer] * @multiplier, PBLevel + 1
    FROM [dbName$BOMLine] BL2
    INNER JOIN CTE ON CTE.[ItemNo] = BL2.[ProdBOMNo]
)

SELECT
CTE.[PBLevel],CTE.[ProdBOMNo],CTE.[ItemNo],CTE.[ItemDescription],CTE.[QtyPer],CTE.[ExtendedQty]

FROM CTE
LEFT JOIN [dbName$BOM] BOM ON BOM.[No_] = CTE.[ProdBOMNo]

• 父项BOM编号为008722
• @multiplier为SSRS输入参数，代表父项需求数量（示例值为8）

当前输出结果 #

期望输出结果 #

修复后的代码 #

declare @multiplier int
set @multiplier = 8; -- 移除字符串引号，避免隐式类型转换

WITH CTE (ProdBOMNo, ItemDescription, ItemNo, QtyPer, ExtendedQty, PBLevel)
     AS
(   
    SELECT BL.[ProdBOMNo], BL.[Descript], BL.[ItemNo], BL.[QtyPer], BL.[QtyPer] * @multiplier, 1 AS PBLevel
    FROM [dbName$BOMLine] BL
    WHERE BL.[ProdBOMNo] = '008722'

    UNION ALL

    SELECT BL2.[ProdBOMNo], BL2.[Descript], BL2.[ItemNo], BL2.[QtyPer], BL2.[QtyPer] * CTE.ExtendedQty, PBLevel + 1
    FROM [dbName$BOMLine] BL2
    INNER JOIN CTE ON CTE.[ItemNo] = BL2.[ProdBOMNo]
)

SELECT
CTE.[PBLevel],CTE.[ProdBOMNo],CTE.[ItemNo],CTE.[ItemDescription],CTE.[QtyPer],CTE.[ExtendedQty]
FROM CTE
LEFT JOIN [dbName$BOM] BOM ON BOM.[No_] = CTE.[ProdBOMNo]

关键修改说明 #

1. 递归扩展量计算逻辑修正：将递归分支中的BL2.[QtyPer] * @multiplier改为BL2.[QtyPer] * CTE.ExtendedQty，让子项的扩展需求量基于父项的累计扩展量计算，而非直接使用顶层乘数。
2. 变量类型修正：原代码中将字符串赋值给int类型变量，存在隐式转换风险，改为直接赋值数值类型。
3. 字段名统一：锚点查询中统一使用PBLevel作为层级字段名，避免与递归分支的字段名歧义。

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