insert @tmp1 select * from 表 where [ID]=@id insert @tmp2 select * from 表 where [ID]=@id while (select count(*) from @tmp2)>0 begin insert @tmp1 select a.* from 表 where PID in (select [id] from @tmp2) insert @tmp3 select * from @tmp2 delete @tmp2 insert @tmp2 select a.* from 表 where PID in (select [id] from @tmp3) delete @tmp3 end select * from @tmp3 order by PID,Sort_ID end ------------------执行 exec mytree 1
select * from 表 order by pid,sort_id
create proc mytree (@id int) as begin declare @tmp1 table ([ID] int,PID int,Sort_ID int) declare @tmp2 table ([ID] int,PID int,Sort_ID int) declare @tmp3 table ([ID] int,PID int,Sort_ID int) insert @tmp1 select * from 表 where [ID]=@id insert @tmp2 select * from 表 where [ID]=@id while (select count(*) from @tmp2)>0 begin insert @tmp1 select a.* from 表 where PID in (select [id] from @tmp2) insert @tmp3 select * from @tmp2 delete @tmp2 insert @tmp2 select a.* from 表 where PID in (select [id] from @tmp3) delete @tmp3 end select * from @tmp3 order by PID,Sort_ID end ------------------执行 exec mytree 1
以上作费,j老师的:(你应该把分给他) declare @tmp1 table ([ID] int,PID int,Sort_ID int) insert @tmp1 select * from tab where [ID]=@id while exists(select 1 from tab a,@tmp b where a.pid=b.Sort_ID and a.Sort_ID not in (select Sort_ID from @tmp)) insert @tmp1 select a.* from tab a,@tmp b where a.pid=b.Sort_ID and a.Sort_ID not in (select Sort_ID from @tmp) select * from @tmp1 order by PID,Sort_ID
1 0 1
2 0 2
5 1 1
4 1 2
6 2 1
7 2 2
3 2 3
select * from tablename order by pid,sort_id
as
begin
declare @tmp1 table ([ID] int,PID int,Sort_ID int)
declare @tmp2 table ([ID] int,PID int,Sort_ID int)
insert @tmp1 select * from 表 where [ID]=@id
insert @tmp2 select * from 表 where [ID]=@id while (select count(*) from @tmp2)>0
begin
insert @tmp1 select a.* from 表 where PID in (select [id] from @tmp2)
insert @tmp3 select * from @tmp2
delete @tmp2
insert @tmp2 select a.* from 表 where PID in (select [id] from @tmp3)
delete @tmp3
end
select * from @tmp3 order by PID,Sort_ID
end
------------------执行
exec mytree 1
as
begin
declare @tmp1 table ([ID] int,PID int,Sort_ID int)
declare @tmp2 table ([ID] int,PID int,Sort_ID int)
declare @tmp3 table ([ID] int,PID int,Sort_ID int)
insert @tmp1 select * from 表 where [ID]=@id
insert @tmp2 select * from 表 where [ID]=@id while (select count(*) from @tmp2)>0
begin
insert @tmp1 select a.* from 表 where PID in (select [id] from @tmp2)
insert @tmp3 select * from @tmp2
delete @tmp2
insert @tmp2 select a.* from 表 where PID in (select [id] from @tmp3)
delete @tmp3
end
select * from @tmp3 order by PID,Sort_ID
end
------------------执行
exec mytree 1
declare @tmp1 table ([ID] int,PID int,Sort_ID int)
insert @tmp1 select * from tab where [ID]=@id
while exists(select 1
from tab a,@tmp b
where a.pid=b.Sort_ID
and a.Sort_ID not in (select Sort_ID from @tmp))
insert @tmp1 select a.*
from tab a,@tmp b
where a.pid=b.Sort_ID
and a.Sort_ID not in (select Sort_ID from @tmp) select * from @tmp1 order by PID,Sort_ID