现在又一张表结构如下：Id 和 ParentId 表示父子关系，只有最底层叶节点num有值，
Id ParentId Name Num
1 0 A NUll
2 1 B NULL
3 1 C NULL
4 2 D 1
5 2 E 2
6 3 F 5
7 3 G 4
我想查询出：父节点num=sum(子节点num）效果如下：
Id ParentId Name Num
1 0 A 12
2 1 B 3
3 1 C 9
4 2 D 1
5 2 E 2
6 3 F 5
7 3 G 4
求SQl语句
在SQL2005 中新增CTE递归不知道怎么用
SQL code:
--> 测试数据：@tb
declare @tb table([Id] int,[ParentId] int,[Name] varchar(1),[Num] int)
insert @tb
select 1,0,'A',null union all
select 2,1,'B',null union all
select 3,1,'C',null union all
select 4,2,'D',1 union all
select 5,2,'E',2 union all
select 6,3,'F',5 union all
select 7,3,'G',4
;WITH Liang AS
(
SELECT Id,ParentId,total=0,flag=Id
from @tb WHERE Num IS NULL
UNION ALL
SELECT A.Id,A.ParentId,B.total+ISNULL(A.Num,0),B.flag
from @tb AS A
JOIN Liang AS B
ON A.ParentId=B.Id
)
UPDATE A SET
Num = B.total
from @tb AS A
JOIN (SELECT flag,SUM(total) AS total
from Liang GROUP BY flag) AS B
ON A.Id=B.flag;
SELECT * from @tb;
/*
Id
