/*-- 得到指定id的子id列表 --*/
--不包含排序字段的情况
create function f_getchildid(@id int)
returns @re table(id int)
as
begin
insert into @re select id from tb where pid=@id
while @@rowcount>0
insert into @re select a.id
from aa inner join @re b on a.pid=b.id
where a.id not in(select id from @re)
return
end
go
declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200),lev int)
insert @tmp1 select *,1 from @a where tc_ID=1
while exists(select 1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1))
insert @tmp1 select a.*,1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
select * from @tmp1