BOM逐级向上汇总应用实例-优快云博客

本文提供了一个SQL递归查询的实例，展示了如何通过递归公用表表达式（CTE）来计算层级结构中各节点的累积得分。该示例首先创建了一个包含层级关系的数据表，并填充了示例数据，然后通过递归查询实现了从根节点到子节点的得分累加。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

IF NOT OBJECT_ID('[tbTest]') IS NULL DROP TABLE [tbTest] GO CREATE TABLE tbTest ( id nvarchar(5), parentID nvarchar(5), score decimal(18,2) ) insert tbTest select '1','',10 union all select '2','1',30 union all select '3','2',30 union all select '4','1',50 union all select '5','',10 union all select '6','5',30 GO /* select * from tbTest id parentID score ----- -------- --------------------------------------- 1 10.00 2 1 30.00 3 2 30.00 4 1 50.00 5 10.00 6 5 30.00 (6 行受影响) */ /* --要得到如下结果 id parentid score ----- -------- --------------------------------------- 1 130.00 2 1 60.00 3 2 30.00 4 1 50.00 5 40.00 6 5 30.00 (6 行受影响) */ ;with t as ( select *,total = score from tbTest as a where not exists(select * from tbTest where a.id = parentid) union all select a.*,cast(b.total + a.score as decimal(18,2)) from tbTest as a join t as b on a.id = b.parentid ) select id,parentid,sum(total) as score from t group by id,parentid order by id /* id parentid score ----- -------- ------------- 1 130.00 2 1 60.00 3 2 30.00 4 1 50.00 5 40.00 6 5 30.00 (6 行受影响) */