求和(父节点是所有子节点的和)

[IFocusYou]

类似表
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

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

[Limpire]

create table #data (ID int,ParentID int,Amount int)
insert #data select 1,0,12
insert #data select 2,1,23
insert #data select 3,1,37
insert #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,132
2,1,83
3,1,37
4,2,60
*/

[Limpire]

非递归：临时表 + 嵌套游标

[汤姆克鲁斯]

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[ParentID] INT,[Amount] INT)
INSERT [tb]
SELECT 1,0,12 UNION ALL
SELECT 2,1,23 UNION ALL
SELECT 3,1,37 UNION ALL
SELECT 4,2,60
GO

--> 测试语句：
; with t as
(
SELECT * FROM [tb] 
union all
select 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
--2
SELECT * FROM [tb] as a cross apply(select sum([Amount]) as [Amount]  from t where a.id=t.id) app
/*
ID	ParentID	Amount	Amount
1	0	12	132
2	1	23	83
3	1	37	37
4	2	60	60
*/

[IFocusYou]

牛人！！谢谢两位

[汤姆克鲁斯]

基于游标的解决方案

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[ParentID] INT,[Amount] INT)
INSERT [tb]
SELECT 1,0,12 UNION ALL
SELECT 2,1,23 UNION ALL
SELECT 3,1,37 UNION ALL
SELECT 4,2,60
GO

DECLARE @result TABLE ([id] INT,[ParentID] INT,[Amount] INT )
DECLARE @id INT,@ParentID INT,@Amount INT,@mid INT,@pid INT,@a INT
INSERT INTO @result SELECT * FROM [tb]  
DECLARE c CURSOR FAST_FORWARD 
FOR
SELECT * FROM [tb] ORDER BY [ParentID] DESC 
OPEN c
FETCH NEXT FROM c INTO @id,@ParentID,@Amount
WHILE @@fetch_status = 0
BEGIN 

	insert into @result
	select b.id,b.[ParentID],t.[Amount] from tb as b join @result t on b.id=t.ParentID
	and b.id=@id
	FETCH NEXT FROM c INTO  @id,@ParentID,@Amount
END	
CLOSE c
DEALLOCATE c
SELECT ID, ParentID, Amount=sum(Amount) from @result group by ID, ParentID
/*
ID	ParentID	Amount
1	0	132
2	1	83
3	1	37
4	2	60
*/