--游戏规则：一颗树，从最明细开始统计上面各级的汇总数，不能用游标
--建立测试数据
Declare @tmpTable Table(DocID varchar(20),ParentID varchar(20),Dep int,[Value] int)
Insert Into @tmpTable(DocID,ParentID,Dep,[Value])
Select '0001','0000',1,null
Union All Select '000101','0001',2,null
Union All Select '00010101','000101',3,null
Union All Select '0001010101','00010101',4,60
Union All Select '0001010102','00010101',4,40
Union All Select '00010102','000101',3,300
Union All Select '00010103','000101',3,200
Union All Select '00010104','000101',3,400
Union All Select '000102','0001',2,null
Union All Select '00010201','000102',3,500
Union All Select '000103','0001',2,null
Union All Select '00010301','000103',3,400
Union All Select '000104','0001',2,200
Select * From @tmpTable
--处理后结果
Declare @tmpReslut Table(DocID varchar(20),ParentID varchar(20),Dep int,[Value] int)
Insert Into @tmpReslut(DocID,ParentID,Dep,[Value])
Select '0001','0000',1,2100
Union All Select '0001
01','0001',2,1000
Union All Select '00010101','000101',3,100
Union All Select '0001010101','00010101',4,60
Union All Select '0001010102','00010101',4,40
Union All Select '00010102','000101',3,300
Union All Select '00010103','000101',3,200
Union All Select '00010104','000101',3,400
Union All Select '000102','0001',2,500
Union All Select '00010201','000102',3,500
Union All Select '000103','0001',2,400
Union All Select '00010301','000103',3,400
Union All Select '000104','0001',2,200
Select * From @tmpReslut
Declare @tmpTable Table(DocID varchar(20),ParentID varchar(20),Dep int,[Value] int)
Insert Into @tmpTable(DocID,ParentID,Dep,[Value])
Select '0001','0000',1,null
Union All Select '000101','0001',2,null
Union All Select '00010101','000101',3,null
Union All Select '0001010101','00010101',4,60
Union All Select '0001010102','00010101',4,40
Union All Select '00010102','000101',3,300
Union All Select '00010103','000101',3,200
Union All Select '00010104','000101',3,400
Union All Select '000102','0001',2,null
Union All Select '00010201','000102',3,500
Union All Select '000103','0001',2,null
Union All Select '00010301','000103',3,400
Union All Select '000104','0001',2,200
--Select * From @tmpTable
;with cte as(
select DocID,ParentID,Dep,[Value] from @tmpTable a where not exists(select 1 from @tmpTable where ParentID=a.DocID)
union all
select a.DocID,a.ParentID,a.Dep,[Value]=b.[Value]+isnull(a.[Value],0) from @tmpTable a inner join cte b on b.parentID=a.DocID
)select DocID,ParentID,Dep,sum([Value])as [Value] from cte group by docid,parentid,dep
/*
DocID ParentID Dep Value
-------------------- -------------------- ----------- -----------
0001 0000 1 2100
000101 0001 2 1000
00010101 000101 3 100
0001010101 00010101 4 60
0001010102 00010101 4 40
00010102 000101 3 300
00010103 000101 3 200
00010104 000101 3 400
000102 0001 2 500
00010201 000102 3 500
000103 0001 2 400
00010301 000103 3 400
000104 0001 2 200
*/
----------------------------------------------------------------------------------------------------------------------------
Declare @tmpTable Table(DocID varchar(20),ParentID varchar(20),Dep int,[Value] int)
Insert Into @tmpTable(DocID,ParentID,Dep,[Value])
Select '0001','0000',1,null
Union All Select '000101','0001',2,null
Union All Select '00010101','000101',3,null
Union All Select '0001010101','00010101',4,60
Union All Select '0001010102','00010101',4,40
Union All Select '00010102','000101',3,300
Union All Select '00010103','000101',3,200
Union All Select '00010104','000101',3,400
Union All Select '000102','0001',2,null
Union All Select '00010201','000102',3,500
Union All Select '000103','0001',2,null
Union All Select '00010301','000103',3,400
Union All Select '000104','0001',2,200
--Select * From @tmpTable
select docid,parentid,dep
,value=(select sum(value) from @tmpTable where docid like a.docid+'%')
from @tmpTable a
/*
docid parentid dep value
-------------------- -------------------- ----------- -----------
0001 0000 1 2100
000101 0001 2 1000
00010101 000101 3 100
0001010101 00010101 4 60
0001010102 00010101 4 40
00010102 000101 3 300
00010103 000101 3 200
00010104 000101 3 400
000102 0001 2 500
00010201 000102 3 500
000103 0001 2 400
00010301 000103 3 400
000104 0001 2 200
*/
-------------------------------------------------------------------------------------------
本文作者:网友 来源:网络
CIO之家 www.ciozj.com 微信公众号:imciow