22,210
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[pid] int,[shu] int)
Insert #T
select 1,0,0 union all
select 2,1,1 union all
select 3,2,1 union all
select 4,2,1 union all
select 5,2,1 union all
select 6,3,1 union all
select 7,3,1
Go
--测试数据结束
;WITH cte AS (
Select *,id AS fullid from #T WHERE id = 1
UNION ALL
SELECT #T.*,cte.fullid FROM #T JOIN cte ON cte.id = #T.pid
)
SELECT fullid AS id ,
0 AS pid ,
SUM(cte.shu) AS shu
FROM cte
GROUP BY fullid;