sql server cte递归分层

。

with cte as
(
select id,  name,1 as lvl from [product]
where Id = 1
union all
select d.id, d.name  ,lvl+1 
from cte c
inner join [product] d
on c.Id = d.[parent_product_id]
)
--左边加‘ ’‘ ’
select (REPLICATE(' ',(lvl-1)*2-LEN(CONVERT(varchar(10),'')))+CONVERT(varchar(10),''))+ name from cte

 

 

 --显示

--示例数据
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中国'
union  all  select 0,'美国'
union  all  select 0,'加拿大'
union  all  select 1,'北京'
union  all  select 1,'上海'
union  all  select 1,'江苏'
union  all  select 6,'苏州'
union  all  select 7,'常熟'
union  all  select 6,'南京'
union  all  select 6,'无锡'
union  all  select 2,'纽约'
union  all  select 2,'旧金山'
go

--级别及排序字段
create function f_id()
returns @re table([id] int,[level] int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select [id],@l,right(10000+[id],4)
from [tb] where [pid]=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l,b.sid+right(10000+a.[id],4)
from [tb] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
return
end
go

--调用函数实现分级显示
select replicate('-',b.[level]*4)+a.name
from [tb] a,f_id()b 
where a.[id]=b.[id]
order by b.sid
go

--删除测试
drop table [tb]
drop function f_id
go

 

。