递归CTE计算层级物料表父项子项成本总和的问题

问题背景 #

需要编写递归查询处理层级结构的物料清单表，将子项成本总和汇总到对应父项，当前递归CTE实现存在Level 0和Level 1成本计算错误。

原始表结构与数据 #

表结构与样本数据 #

测试临时表创建代码 #

DECLARE @tmp TABLE 
(
    [COMPOSANT] [varchar](25) NULL, 
    [COMPOSE] [varchar](25) NULL, 
    [QTE] [numeric](38, 9) NULL,
    [PA] [numeric](38, 9) NULL,
    [COST] [numeric](38, 9) NULL,
    [LEVEL] [int] NULL
)

INSERT INTO @tmp VALUES ('PARENT1', 'CHILD1', 24, NULL, NULL, 0);
INSERT INTO @tmp VALUES ('PARENT1', 'CHILD2', 2, NULL, NULL, 0);
INSERT INTO @tmp VALUES ('CHILD1', 'CHILD11', 10, NULL, NULL, 1);
INSERT INTO @tmp VALUES ('CHILD1', 'CHILD12', 4, 3, 12, 1);
INSERT INTO @tmp VALUES ('CHILD11', 'CHILD111', 100, 1, 100, 2);

SELECT * FROM @tmp

预期结果 #

总COST应为：24288 + 100 = 24388

现有问题代码 #

WITH CTE_HIERARCHIE AS (
    -- Feuilles avec PA connus
    SELECT 
        COMPOSE AS COMPOSE,
        COMPOSANT,
        QTE,
        PA,
        cast(PA * QTE as [numeric](38, 9)) AS COST,
        0 AS INVLEVEL
        ,LEVEL
    FROM @tmp
    WHERE PA IS NOT NULL

    UNION ALL

    -- Propagation vers les parents
    SELECT 
        parent.COMPOSE AS COMPOSE,
        parent.COMPOSANT,
        parent.QTE,
        cast(child.COST as numeric (38, 9)) as PA,
        cast(  child.PA*child.QTE as [numeric](38, 9)) AS COST,
        child.INVLEVEL + 1,parent.LEVEL
    FROM @tmp parent
    INNER JOIN CTE_HIERARCHIE child
        ON parent.COMPOSE = child.COMPOSANT
)

-- Résumé des coûts par composant parent
SELECT 
    COMPOSANT,COMPOSE,QTE,SUM(PA) as PA,
    SUM(COST) as COST,LEVEL
FROM CTE_HIERARCHIE
GROUP BY COMPOSANT,COMPOSE,LEVEL,QTE
ORDER BY LEVEL,COMPOSANT,COMPOSE;

正确递归查询方案 #

核心思路 #

1. 从最底层的叶子节点开始（即PA不为空的节点），保留其原始成本数据
2. 递归向上遍历父项，计算父项对应子项的总成本（子项总成本×父项数量）
3. 汇总每个父项的所有子项总成本，得到父项的PA，再计算父项的COST（PA×自身数量）
4. 合并叶子节点与计算后的父项数据，得到完整结果

修正后的代码 #

WITH CTE_BOM AS (
    -- 锚点成员：叶子节点（已明确PA的项）
    SELECT 
        COMPOSANT,
        COMPOSE,
        QTE,
        PA,
        COST,
        LEVEL,
        CAST(COST AS NUMERIC(38,9)) AS TOTAL_COST
    FROM @tmp
    WHERE PA IS NOT NULL

    UNION ALL

    -- 递归成员：向上遍历父项，计算对应子项的总成本
    SELECT 
        parent.COMPOSANT,
        parent.COMPOSE,
        parent.QTE,
        NULL AS PA,
        NULL AS COST,
        parent.LEVEL,
        CAST(child.TOTAL_COST * parent.QTE AS NUMERIC(38,9)) AS TOTAL_COST
    FROM @tmp parent
    INNER JOIN CTE_BOM child
        ON parent.COMPOSE = child.COMPOSANT
),
CTE_SUMMARY AS (
    -- 汇总非叶子节点的子项总成本，计算PA和COST
    SELECT 
        COMPOSANT,
        COMPOSE,
        QTE,
        SUM(TOTAL_COST) AS PA,
        SUM(TOTAL_COST) * QTE AS COST,
        LEVEL
    FROM CTE_BOM
    WHERE PA IS NULL
    GROUP BY COMPOSANT, COMPOSE, QTE, LEVEL

    UNION ALL

    -- 合并叶子节点的原始数据
    SELECT 
        COMPOSANT,
        COMPOSE,
        QTE,
        PA,
        COST,
        LEVEL
    FROM @tmp
    WHERE PA IS NOT NULL
)

-- 按层级排序输出最终结果
SELECT 
    COMPOSANT,
    COMPOSE,
    QTE,
    PA,
    COST,
    LEVEL
FROM CTE_SUMMARY
ORDER BY LEVEL, COMPOSANT, COMPOSE;

代码说明 #

• 锚点成员：选取所有已明确采购单价（PA）的叶子节点，直接使用原始COST作为子项总成本TOTAL_COST
• 递归成员：关联父项与子项，计算父项对应该子项的总成本（子项总成本×父项使用数量）
• CTE_SUMMARY：先汇总非叶子节点的所有子项总成本得到父项的PA，再计算父项的COST，最后合并叶子节点的原始数据
• 最终结果按层级、父项名称、子项名称排序，与预期完全匹配

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