create table a
(
fid int,
id int,
name varchar(50)
)
insert into a
select null,1,'浙江'
union all
select 1,2,'杭州'
union all
select 1,3,'温州'
union all
select 1,4,'绍兴'
union all
select 2,5,'下沙'
union all
select 4,6,'上虞'
with tb (id,n1,n2,level)
as (
select id,name,name,level=1
from a
where fid is null
union all
select a.id,
cast(tb.n1+a.name as varchar(50)),
cast(replicate('---',level*2)+a.name as varchar(50)),
level+1
from a,tb
where tb.id=a.fid
)
select * from tb order by n1
结果: