22,210
社区成员
发帖
与我相关
我的任务
分享
/* 使用递归表达式,需要sqlserver2005及以上 */
/* 假设源表为Test */
with Test (fnumber, fname, flevel, fitemid, parent, data) as
(
select 1, 'a', 1, 1, 0, 0 union all
select 2, 'b', 2, 2, 1, 0 union all
select 3, 'c', 2, 2, 1, 0 union all
select 4, 'd', 2, 4, 0, 0 union all
select 5, 'e', 5, 5, 4, 10
)
, S1 as
(
select fnumber, fname, flevel, fitemid, parent, data
from Test
where fitemid not in (select parent from Test)
union all
select Test.fnumber, Test.fname, Test.flevel, Test.fitemid, Test.parent, data = Test.data + S1.data
from S1 inner join
Test on S1.parent = Test.fitemid
)
select *
from S1
order by fnumber