您的位置: 首页  >  IT文章  >  【SQL】游戏规则：一颗树，从最明细开始统计上面各级的汇总数，不能用游标，有测试数据,该怎么解决

【SQL】游戏规则：一颗树，从最明细开始统计上面各级的汇总数，不能用游标，有测试数据,该怎么解决

分类: IT文章 • 2023-10-28 21:20:42
【SQL】游戏规则：一颗树，从最明细开始统计上面各级的汇总数，不能用游标，有测试数据
直接上测试数据，一级一级的从最明细开始汇总

请各位高手给点算法，谢谢

SQL code

--游戏规则：一颗树，从最明细开始统计上面各级的汇总数，不能用游标
--建立测试数据
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 '000101','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


------解决方案--------------------
SQL code
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
*/

------解决方案--------------------
探讨
SQL code
;with cte as --请问这个是什么意思啊？

------解决方案--------------------
SQL code

相关推荐