Sql Server level查询(转)

转自：http://bbs.csdn.net/topics/320023621

if OBJECTPROPERTY(object_id('tb'),'isusertable')<>0 
  drop table tb
create table tb(ybh nvarchar(10),ebh nvarchar(10),beizhu nvarchar(1000))
insert tb
select '0001',null,'云南省'
union all select '0002','0001','昆明市'
union all select '0003','0001','昭通市'
union all select '0009','0001','大理市'
union all select '0008',null,'四川省'
union all select '0004',null,'贵州省'
union all select '0005','0002','五华区'
union all select '0007','0002','水富县'
union all select '0006','0005','西园路192号'
union all select '0010','0006','金色梧桐'
union all select '0011','0010','科技有限公司'
union all select '0015','0007','两碗乡'
union all select '0013','0015','两碗村'
union all select '0012','0013','某跨国集团董事长'
union all select '0014','0008','成都市'

--select * from tb
--广度排序(先显示第一层节点，再显示第二次节点......)
--定义辅助表
declare @level_tb table(bh nvarchar(10),level int)
declare @level int
set @level=0
insert @level_tb(bh,level)
select ybh,@level from tb where ebh is null
while @@ROWCOUNT>0
  begin
    set @level=@level+1
    insert @level_tb(bh,level)
      select ybh,@level 
        from tb a,@level_tb b
        where a.ebh=b.bh 
              and b.level=@level-1
  end 
  select a.*,b.* from tb a,@level_tb b where a.ybh=b.bh order by level

--以下是自己的例子，没有环境你可能不能正常运行：

declare @level_tb table(lov_id varchar(50),lov_name varchar(200),level int)
declare @level int
set @level=0
insert @level_tb(lov_id,lov_name,level)
select t.row_id,t.LOV_NAME,@level from T_SYS_LIST_OF_VAL t where t.LOV_TYPE ='ADMINISTRATIVE_DIVISION' and t.PAR_LOV_ID=1
while @@ROWCOUNT>0
  begin
    set @level=@level+1
    insert @level_tb(lov_id,lov_name,level)
      select a.row_id,b.lov_name,@level 
        from T_SYS_LIST_OF_VAL a,@level_tb b
        where a.PAR_LOV_ID=b.lov_id 
              and b.level=@level-1
  end  
select a.row_id,a.LOV_NAME,a.LOV_VAL,b.level 
from T_SYS_LIST_OF_VAL a,@level_tb b where a.ROW_ID=b.lov_id order by level