Sql Server level查询(转)
转自：http://bbs.csdn.net/topics/320023621if 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
页:
[1]