递归就是循环引用。一般都有个起点和终点。 这里的起点就是union all前面的那句 select DocID,ParentID,Dep,[Value] from @tmpTable a where not exists(select 1 from @tmpTable where ParentID=a.DocID) 而终点就使cte表和@tmpTable两个结果集无交集。---- 微软对于递归支持的反应已经很落后了。
CET很好用,可以看看联机帮助。
这样好看一点: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 关注各位对cte递归的解释。
这里的起点就是union all前面的那句
select DocID,ParentID,Dep,[Value] from @tmpTable a where not exists(select 1 from @tmpTable where ParentID=a.DocID) 而终点就使cte表和@tmpTable两个结果集无交集。----
微软对于递归支持的反应已经很落后了。
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 关注各位对cte递归的解释。