查找树折点表低层数据

 create table abc(

id int,

tname varchar(20),

parentid int

)

insert into abc select 1,'a',-1

union all 

select 2,'b',1

union all

select 3,'c',1

union all

        select 4,'d',2

union all

select 5,'e',2

union all 

select 6,'f',3

union all

select 7,'g',3

select * from abc

exec index_base 1

create proc index_base

(

@id int

)

as

   select id ,IDENTITY(int, 1,1) AS ID_no into #tb1 from abc where parentid=@id

   select id into #tb2 from abc where 1=2

while ((select count(1) from #tb1)<>0 )

begin

    if (select count(1) from abc where parentid = (select top 1 id from #tb1 order by ID_no asc) )<>0

begin

insert into #tb1(id) select id from abc where parentid = (select top 1 id from #tb1 order by ID_no asc)

delete #tb1 where id = (select top 1 id from #tb1 order by ID_no asc)

end

else

begin

insert #tb2 select top 1 id from #tb1 order by ID_no asc

delete #tb1 where id = (select top 1 id from #tb1 order by ID_no asc)

end

end

select * from abc where id in(select * from #tb2)

drop table #tb1

drop table #tb2

GO