insert @tmp1 select * from new_tree where parent = @parent insert @tmp3 select * from new_tree where parent = @parent /*循环的次数等于树的深度*/ while exists(select * from @tmp1 where child is not NULL) begin insert @tmp2 select a.* from new_tree a,@tmp1 b where a.parent = b.child /*@tmp2表中存本次查询的层次的所有结点*/ delete from @tmp1 where child is not NULL /*@tmp1表中最终存的是叶子结点*/ insert @tmp1 select * from @tmp2 /*@tmp3表中最保存每次查到的子孙*/ insert @tmp3 select * from @tmp2 delete from @tmp2 end if @mode =0 select * from @tmp3 else select * from @tmp1 set nocount off end goproc_new_tree '1',1 go
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
go
/*parent对应父亲结点,child对应儿子结点,如果child is NULL则本结点为叶子结点*/
create table new_tree ( parent varchar(80), child varchar(80))
go
insert new_tree values ( '1','2');
insert new_tree values ( '1','3');
insert new_tree values ( '2','4');
insert new_tree values ( '2','5');
insert new_tree values ( '3','6');
insert new_tree values ( '3','7');
insert new_tree values ( '3','8');
insert new_tree values ( '6','9');
insert new_tree values ( '5','10');
insert new_tree values ( '4','11');
insert new_tree values ( '9','12');
insert new_tree values ( '7',NULL);
insert new_tree values ( '8',NULL);
insert new_tree values ( '10',NULL);
insert new_tree values ( '11',NULL);
insert new_tree values ( '12',NULL);drop proc proc_new_tree
go
/*@parent 输入根结点标识,@mode为0 则输出为所有子孙记录,否则输出所有叶子结点*/
create proc proc_new_tree (@parent varchar(80),@mode int =0)
asbegin
set nocount on
/*如果不是SQLSERVER2000可以用临时表*/
declare @tmp1 table ( parent varchar(80), child varchar(80))
declare @tmp2 table ( parent varchar(80), child varchar(80))
declare @tmp3 table ( parent varchar(80), child varchar(80))
insert @tmp1 select * from new_tree where parent = @parent
insert @tmp3 select * from new_tree where parent = @parent /*循环的次数等于树的深度*/
while exists(select * from @tmp1 where child is not NULL)
begin
insert @tmp2 select a.* from new_tree a,@tmp1 b where a.parent = b.child
/*@tmp2表中存本次查询的层次的所有结点*/
delete from @tmp1 where child is not NULL
/*@tmp1表中最终存的是叶子结点*/
insert @tmp1 select * from @tmp2
/*@tmp3表中最保存每次查到的子孙*/
insert @tmp3 select * from @tmp2
delete from @tmp2
end
if @mode =0 select * from @tmp3
else select * from @tmp1
set nocount off
end
goproc_new_tree '1',1
go
1 0 1
2 0 2
3 2 3
4 1 2
5 1 1
6 2 1
7 2 2我想要的结果顺序为:
ID PID Sort_ID
1 0 1
5 1 1
4 1 2
6 2 1
7 2 2
3 2 3create 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)
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
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