22,209
社区成员
发帖
与我相关
我的任务
分享
create table t1(id int,[name] varchar(100),parent int)
insert into t1
select 10000,'xx1',0 union all
select 10001,'xx2',10000 union all
select 10002,'xx3',10001 union all
select 10003,'xx4',10001 union all
select 10004,'xx5',10003 union all
select 10005,'xx6',10000
go
create table t2(id int,cnt int)
insert into t2
select 10001,5 union all
select 10002,4 union all
select 10003,10 union all
select 10004,20 union all
select 10005,12
go
;with cte as
(
select id,cnt,id as po from t2
union all
select t.id,b.cnt,a.id
from t1 a join cte t on a.parent = t.po
join t2 b on a.id = b.id
)
select id,sum(cnt) cnt
from cte
group by id
drop table t1,t2
/****************
id cnt
----------- -----------
10001 39
10002 4
10003 30
10004 20
10005 12
(5 行受影响)