drop table new_tree
go
create table new_tree ( ParentID varchar(80), PartNo varchar(80),LinkQty int,HasChild tinyint)
go
insert new_tree values ( '1','2',2,1);
insert new_tree values ( '1','3',2,1);
insert new_tree values ( '2','4',2,1);
insert new_tree values ( '2','5',2,1);
insert new_tree values ( '3','6',2,1);
insert new_tree values ( '3','7',2,1);
insert new_tree values ( '3','8',2,1);
insert new_tree values ( '6','9',2,1);
insert new_tree values ( '5','10',2,1);
insert new_tree values ( '4','11',2,1);
insert new_tree values ( '9','12',2,1);
insert new_tree values ( '7',NULL,2,1);
insert new_tree values ( '8',NULL,2,1);
insert new_tree values ( '10',NULL,2,1);
insert new_tree values ( '11',NULL,2,1);
insert new_tree values ( '12',NULL,2,1);
drop proc proc_new_tree
go
create proc proc_new_tree (@ParentID varchar(80))
as
begin
set nocount on
declare @tmp1 table ( ParentID varchar(80), PartNo varchar(80),LinkQty int,HasChild tinyint)
declare @tmp2 table ( ParentID varchar(80), PartNo varchar(80),LinkQty int,HasChild tinyint)
declare @tmp3 table ( ParentID varchar(80), PartNo varchar(80),LinkQty int,HasChild tinyint)
insert @tmp1 select * from new_tree where ParentID = @ParentID
insert @tmp3 select * from new_tree where ParentID = @ParentID
while exists(select * from @tmp1 where PartNo is not NULL)
begin
insert @tmp2 select a.* from new_tree a,@tmp1 b where a.ParentID = b.PartNo
delete from @tmp1 where PartNo is not NULL
insert @tmp1 select * from @tmp2
insert @tmp3 select * from @tmp2
delete from @tmp2
end
select * from @tmp1 union select * from @tmp3
set nocount off
end
go
proc_new_tree '5'
go
加了注释
drop table new_tree
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)
as
begin
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
go
proc_new_tree '1',1
go