--> 测试数据: [tb] if object_id('[tb]') is not null drop table [tb] create table [tb] (parent int,child int,name varchar(4)) insert into [tb] select 0,1,'中国' union all select 0,2,'美国' union all select 1,3,'广东' union all select 1,4,'广西' union all select 3,5,'珠海' union all select 5,6,'拱北' go;with wsp as ( select px=child,lev=cast(1 as varchar),* from tb b where parent=0 union all select px=a.px,lev=cast(a.lev+ltrim(row_number() over(order by b.child)) as varchar),b.* from wsp a,tb b where a.child=b.parent ) select parent,child,name from wsp order by px,lev --结果: parent child name ----------- ----------- ---- 0 1 中国 1 3 广东 3 5 珠海 5 6 拱北 1 4 广西 0 2 美国
if object_id('[tb]') is not null drop table [tb]
create table [tb] (parent int,child int,name varchar(4))
insert into [tb]
select 0,1,'中国' union all
select 0,2,'美国' union all
select 1,3,'广东' union all
select 1,4,'广西' union all
select 3,5,'珠海' union all
select 5,6,'拱北'
go;with wsp
as
(
select px=child,lev=cast(1 as varchar),* from tb b where parent=0
union all
select px=a.px,lev=cast(a.lev+ltrim(row_number() over(order by b.child)) as varchar),b.* from wsp a,tb b where a.child=b.parent
)
select parent,child,name from wsp order by px,lev
--结果:
parent child name
----------- ----------- ----
0 1 中国
1 3 广东
3 5 珠海
5 6 拱北
1 4 广西
0 2 美国