create procedure uPro
@pc int
as
set nocount ondeclare @step int
set @step=1
create table #tp(pc int,step int)
insert into #tp
select @pc ,0while @step>0
begin
insert into #tp
select distinct code,@step from uTable where parentcode in (select pc from #tp where step=@step-1)
set @step= case @@rowcount when 0 then 0 else @step+1 end
endselect pc from #tp where step>0drop table #tp
@pc int
as
set nocount ondeclare @step int
set @step=1
create table #tp(pc int,step int)
insert into #tp
select @pc ,0while @step>0
begin
insert into #tp
select distinct code,@step from uTable where parentcode in (select pc from #tp where step=@step-1)
set @step= case @@rowcount when 0 then 0 else @step+1 end
endselect pc from #tp where step>0drop table #tp
@pc int
as
set nocount ondeclare @step int
set @step=1
create table #tp(pc int,step int)
insert into #tp
select @pc ,0while @step>0
begin
insert into #tp
select distinct code,@step from uTable where parentcode in (select pc from #tp where step=@step-1)
set @step= case @@rowcount when 0 then 0 else @step+1 end
enddeclare @str varchar(500)
set @str=''
select @str=@str+pc +';'from #tp where step>0
drop table #tp
return @str
insert @a values(2,3)
insert @a values(2,4)
insert @a values(2,5)
insert @a values(3,12)
insert @a values(3,15)
insert @a values(4,24)
insert @a values(4,26)
insert @a values(5,35)
insert @a values(5,36)
insert @a values(36,99)declare @tmp1 table (a int)
insert @tmp1 select code from @a where parentcode=2
while exists(select 1
from @a a,@tmp1 b
where a.parentcode=b.a
and a.code not in (select a from @tmp1))
insert @tmp1 select a.code
from @a a,@tmp1 b
where a.parentcode=b.a
and a.code not in (select a from @tmp1)
select * from @tmp1 order by a
我作了一下测试,各位的方法,在数据量较小时,速度很快,在数据量一般的时候,也还可以,但是数据量稍大时,就会显得效率较低,可能是因为在存储过程中把所有的结果放在一个表中,而且在每次查询的时候都用到了In字句,In的效率很低,所以导致了随着数据量的增大,速度会成几何级数的增加,有没有更好的办法可以在数据量大时,效率较高的,请赐教!!!!!不如查询到的code数量在1-3万条左右,递归的次数在十次左右,请帮忙!!!!!