--测试环境
declare @test table(id int,name char(10),parentid int)
--测试数据
insert @test
select 1 ,'aa' ,0
union all
select 2 ,'bb' ,0
union all
select 3 ,'aa1' ,1
union all
select 4 ,'aa2' ,3
union all
select 5 ,'bb1' ,2
union all
select 6 ,'bb2' ,5
--实现
declare @cs int
set @cs = 0
declare @tree table(id int,cs int,iid int)
Insert @tree Values(0,0,0)
While (@@ROWCOUNT >= 1)
Begin
select @cs = @cs + 1
Insert @tree(id,CS,iid)
Select a.id,@cs,
case when b.cs = 0 then a.id else b.iid end
From @test a,@tree b
Where a.parentid = b.id and b.CS = @cs - 1 and a.id <> a.parentid
End
--结果
select t.name from @tree tr join @test t
on tr.id = t.id and tr.cs > 0
order by tr.iid,tr.cs