有这样一个表
ID NUM PARENTID
1 2 -1
2 4 -1
3 6 1
4 8 1
5 10 2
6 12 2
7 14 3
8 16 3
9 18 4
10 20 4
........................
整个表是个树型结构,PARENTID为这个节点的父节点,每个节点上都可能有数据,现要实现能够分级统计。
如按一级子节点统计的结果如下
-------------------------
ID NUM
1 84 (20+18+16+14+8+6+2)
2 26 (4+10+12)
如按子节点1下的子节点统计结果如下
-------------------------
ID NUM
3 36 (6+14+16)
4 46 (8+18+20)
个人觉得有点难,要用到递归...请高手帮忙,谢谢~~
ID NUM PARENTID
1 2 -1
2 4 -1
3 6 1
4 8 1
5 10 2
6 12 2
7 14 3
8 16 3
9 18 4
10 20 4
........................
整个表是个树型结构,PARENTID为这个节点的父节点,每个节点上都可能有数据,现要实现能够分级统计。
如按一级子节点统计的结果如下
-------------------------
ID NUM
1 84 (20+18+16+14+8+6+2)
2 26 (4+10+12)
如按子节点1下的子节点统计结果如下
-------------------------
ID NUM
3 36 (6+14+16)
4 46 (8+18+20)
个人觉得有点难,要用到递归...请高手帮忙,谢谢~~
Create Table Tree
(ID Int,
NUM Int,
PARENTID Int)
Insert Tree Select 1, 2, -1
Union All Select 2, 4, -1
Union All Select 3, 6, 1
Union All Select 4, 8, 1
Union All Select 5, 10, 2
Union All Select 6, 12, 2
Union All Select 7, 14, 3
Union All Select 8, 16, 3
Union All Select 9, 18, 4
Union All Select 10, 20, 4
GO
--建立函數
Create Function GetNUM(@ID Int)
Returns Int
As
Begin
Declare @NUM Int
Declare @Tree Table(ID Int,NUM Int, PARENTID Int)
Insert @Tree Select * From Tree Where ID = @ID
While @@ROWCOUNT > 0
Insert @Tree Select A.* From Tree A Inner Join @Tree B On A.PARENTID = B.ID Where A.ID Not In (Select Distinct ID From @Tree)
Select @NUM = SUM(NUM) From @Tree
Return @NUM
End
GO
--測試
Select
ID,
dbo.GetNUM(ID) As NUM
From Tree
GO
--刪除測試環境
Drop Table Tree
Drop Function GetNUM
--結果
/*
ID NUM
1 84
2 26
3 36
4 46
5 10
6 12
7 14
8 16
9 18
10 20
*/