sql 递归查询，显示树结构

sql 递归查询，显示树结构

go
declare @pp int
with parent as (
select 编码,名称,上级编码,
ROW_NUMBER() over(order by 编码)as pid,1 as plevel,
cast(right(‘00’+cast(ROW_NUMBER() over(order by 编码)as nvarchar(4000)),3) as nvarchar(4000)) as pn
from TType where 上级编码=0
union all
select tz.编码,tz.名称,tz.上级编码,
ROW_NUMBER() over(order by tz.编码),parent.plevel+1,
parent.pn+right(‘000’+cast(ROW_NUMBER() over(order by tz.编码) as nvarchar(4000)),4)
from TType as tz join parent
on tz.上级编码=parent.编码
)
select * from parent order by pn