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

--游戏规则：一颗树，从最明细开始统计上面各级的汇总数，不能用游标--建立测试数据Declare @tmpTable Table(DocID varchar(20),ParentID varchar(20),Dep int,[Value] int)Insert Into @tmpTable(DocID,ParentID,Dep,[Value])Select '0001','0000',1,nullUnion All Select '000101','0001',2,nullUnion All Select '00010101','000101',3,nullUnion All Select '0001010101','00010101',4,60Union All Select '0001010102','00010101',4,40Union All Select '00010102','000101',3,300Union All Select '00010103','000101',3,200Union All Select '00010104','000101',3,400Union All Select '000102','0001',2,nullUnion All Select '00010201','000102',3,500Union All Select '000103','0001',2,nullUnion All Select '00010301','000103',3,400Union All Select '000104','0001',2,200Select * 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,2100Union All Select '000101','0001',2,1000Union All Select '00010101','000101',3,100Union All Select '0001010101','00010101',4,60Union All Select '0001010102','00010101',4,40Union All Select '00010102','000101',3,300Union All Select '00010103','000101',3,200Union All Select '00010104','000101',3,400Union All Select '000102','0001',2,500Union All Select '00010201','000102',3,500Union All Select '000103','0001',2,400Union All Select '00010301','000103',3,400Union All Select '000104','0001',2,200Select * 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,nullUnion All Select '000101','0001',2,nullUnion All Select '00010101','000101',3,nullUnion All Select '0001010101','00010101',4,60Union All Select '0001010102','00010101',4,40Union All Select '00010102','000101',3,300Union All Select '00010103','000101',3,200Union All Select '00010104','000101',3,400Union All Select '000102','0001',2,nullUnion All Select '00010201','000102',3,500Union All Select '000103','0001',2,nullUnion All Select '00010301','000103',3,400Union 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 allselect 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           2100000101               0001                 2           100000010101             000101               3           1000001010101           00010101             4           600001010102           00010101             4           4000010102             000101               3           30000010103             000101               3           20000010104             000101               3           400000102               0001                 2           50000010201             000102               3           500000103               0001                 2           40000010301             000103               3           400000104               0001                 2           200*/
------解决方案--------------------
探讨

SQL code
;with cte as  --请问这个是什么意思啊？

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