求和(父节点是全部子节点的和)_Sql Server

求和(父节点是所有子节点的和)
类似表
ID,ParentID,Amount
1,0,12
2,1,23
3,1,37
4,2,60

结果
1,0,132
2,1,83
3,1,37
4,2,60

最好有递归与非递归两种算法

------解决方案--------------------

SQL code

create table #data (ID int,ParentID int,Amount int)insert #data select 1,0,12insert #data select 2,1,23insert #data select 3,1,37insert #data select 4,2,60;with cte as(    select mid=ID, pid=ParentID, * from #data    union all    select a.mid, a.pid, b.* from cte a join #data b on a.ID=b.ParentID)select ID=mid, ParentID=pid, Amount=sum(Amount) from cte group by mid, pid/*结果1,0,1322,1,833,1,374,2,60*/
------解决方案--------------------
SQL code--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GOCREATE TABLE [tb]([ID] INT,[ParentID] INT,[Amount] INT)INSERT [tb]SELECT 1,0,12 UNION ALLSELECT 2,1,23 UNION ALLSELECT 3,1,37 UNION ALLSELECT 4,2,60GO--> 测试语句：; with t as(SELECT * FROM [tb] union allselect b.id,b.[ParentID],t.[Amount] from tb as b join t on b.id=t.ParentID)--1--SELECT ID, ParentID, Amount=sum(Amount) from t group by ID, ParentID--2SELECT * FROM [tb] as a cross apply(select sum([Amount]) as [Amount]  from t where a.id=t.id) app/*ID    ParentID    Amount    Amount1    0    12    1322    1    23    833    1    37    374    2    60    60*/