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
--开始结点
insert @tmp1 select * from @a where tc_ID=10
--循环得到
while exists(select 1 from @a a,@tmp1 b where a.tc_id=b.tc_pID and a.tc_ID not in (select tc_ID from @tmp1))
insert @tmp1 select a.* from @a a,@tmp1 b where a.tc_id=b.tc_pID and a.tc_ID not in (select tc_ID from @tmp1)
declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200))
while exists(select 1 from @a where TC_PID=0 and tc_id not in (select tc_id from @tmp1))
begin
insert @tmp1 select top 1 * from @a where TC_PID=0 and tc_id not in (select tc_id from @tmp1) order by TC_Id
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.* from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
end
select * from @tmp1