SQL Server产品层级自底向上递归求和：仅最低级SKU有值的聚合实现

需求说明 #

维护的产品层级SKU树形结构满足以下规则：

• 仅最底层IsSku=1的SKU节点存储实际消耗值Volume
• 上层分类节点的Volume初始值为0
• 需要自底向上递归聚合，得到每个节点下所有SKU的Volume总和

推荐实现方案 #

采用从SKU节点向上递归遍历所有父节点的思路实现，相比原路径匹配自连接方案性能更优，尤其适合节点数量较多的场景：

DECLARE @tblData TABLE
(
    [ID] INT NOT NULL,
    [ParentId] INT NULL,
    [Name] varchar(50) NOT NULL,
    [Volume] int NOT NULL,
    [IsSku] bit
)

INSERT INTO @tblData
VALUES 
 (1,-1,'All',0,0)
,(2,1,'Cat A',0,0)  
,(3,1,'Cat B',0,0)  
,(4,2,'Cat A.1',0,0)  
,(5,2,'Cat A.2',0,0)  
,(6,3,'Cat B.1',0,0)  
,(7,3,'Cat B.2',0,0)  
,(8,4,'SKU1',10,1)  
,(9,4,'SKU2',5,1)  
,(10,5,'SKU3',7,1)  
,(11,5,'SKU4',4,1)  
,(12,6,'SKU1',10,1)  
,(13,6,'SKU2',5,1)  
,(14,7,'SKU3',7,1)  
,(15,7,'SKU4',4,1)  

;WITH SkuVolumeCTE AS (
    -- 锚点：取所有SKU节点本身的Volume
    SELECT
        ID AS NodeId,
        ParentId,
        Volume AS SkuVolume
    FROM @tblData
    WHERE IsSku = 1

    UNION ALL

    -- 递归：向上查找每个节点的父节点，将SKU的Volume归属到上级节点
    SELECT
        t.ID AS NodeId,
        t.ParentId,
        svc.SkuVolume
    FROM @tblData t
    INNER JOIN SkuVolumeCTE svc ON t.ID = svc.ParentId
    WHERE svc.ParentId != -1 -- 终止条件：遍历到根节点的父级停止
)
-- 按节点分组求和，关联原表补全字段信息
SELECT
    t.ID,
    t.ParentId,
    t.Name,
    t.IsSku,
    SUM(svc.SkuVolume) AS TotalVolume
FROM @tblData t
INNER JOIN SkuVolumeCTE svc ON t.ID = svc.NodeId
GROUP BY t.ID, t.ParentId, t.Name, t.IsSku
ORDER BY t.ID

样例输出结果 #

原代码优化说明 #

原有基于路径匹配的方案逻辑正确，仅存在性能短板：当树形节点数量超过千级时，路径字符串匹配的自连接操作效率会大幅下降。如果节点数量较少，原有代码可直接使用，仅需要把输出字段别名改为TotalVolume即可。

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