做个函数
create function getid(@id int)
returns @t table(id int)
as
begin
declare @r (id int,level int)
delcare @l int
set @l=0
insert into @r select @id,@l
while exists(select 1 from tablename a join @r b on a.parentid=b.id and b.level=@l)
begin
insert into @t select id from tablename a join @r b on a.parentid=b.id and b.level=@l
set @l=@l+1
insert into @r select id,@l from tablename a join b on a.parentid=b.id and b.level=@l -1
end
return
endgo
select * from dbo.getid(1)
create function getid(@id int)
returns @t table(id int)
as
begin
declare @r (id int,level int)
delcare @l int
set @l=0
insert into @r select @id,@l
while exists(select 1 from tablename a join @r b on a.parentid=b.id and b.level=@l)
begin
insert into @t select id from tablename a join @r b on a.parentid=b.id and b.level=@l
set @l=@l+1
insert into @r select id,@l from tablename a join b on a.parentid=b.id and b.level=@l -1
end
return
endgo
select * from dbo.getid(1)
id int,
ParentID int
) insert tbtree (ID,ParentID) values ( 1,0)insert tbtree (ID,ParentID) values ( 2,1)insert tbtree (ID,ParentID) values ( 3,1)insert tbtree (ID,ParentID) values ( 4,1)insert tbtree (ID,ParentID) values ( 5,1)insert tbtree (ID,ParentID) values ( 6,5)insert tbtree (ID,ParentID) values ( 7,1)insert tbtree (ID,ParentID) values ( 8,7)insert tbtree (ID,ParentID) values ( 9,5)insert tbtree (ID,ParentID) values ( 10,4)insert tbtree (ID,ParentID) values ( 11,1)insert tbtree (ID,ParentID) values ( 12,11)insert tbtree (ID,ParentID) values ( 13,1)insert tbtree (ID,ParentID) values ( 14,6)insert tbtree (ID,ParentID) values ( 15,5)
alter PROCEDURE GetTree (
@ID int)
as
set nocount on
create table #tmp (Id int,ParentID int)
insert into #tmp select * from tbtree where ID=@ID
while exists(select 1 from tbtree a,#tmp b where a.ParentID=b.ID and a.ID not in (select ID from #tmp))
insert #tmp select a.* from tbtree a,#tmp b where a.ParentID=b.ID and a.ID not in (select ID from #tmp)
select * from #tmp
set nocount off
GOGetTree 1