树的宽度优先遍历

 /*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