SQL递归求和问题

hml2007 2010-02-26 12:59:55

现在又一张表结构如下：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语句

...全文

285 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

快乐_石头 2010-02-26

打赏
举报

--> Title  : Generating test data 

--> Author : 

--> Date   : 2010-02-26 13:19:40

 

if not object_id('tB') is null

	drop table tB

Go

Create table tB([Id] int,[ParentId] int,[Name] nvarchar(1),[Num] int)

Insert tB

select 1,0,N'A',null union all

select 2,1,N'B',null union all

select 3,1,N'C',null union all

select 4,2,N'D',1 union all

select 5,2,N'E',2 union all

select 6,3,N'F',5 union all

select 7,3,N'G',4

Go

while exists(select 1 from tb where num is null)

  begin

  update tb set num=b.num

  from(select ParentId,

       sum(Num)Num

       from tb 

       group by ParentId)b

  where id=b.ParentId

  end

go

select * from tb

/*

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



(7 個資料列受到影響)



*/

you_tube 2010-02-26

打赏
举报

UP！

liangCK 2010-02-26

打赏
举报

--> 测试数据：@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          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



(7 行受影响)





*/