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