create table tb(id int ,name varchar ( 10 ),parentid int ) insert tb select 1 , ' aaaa ' , 0 union all select 2 , ' bbbb ' , 0 union all select 3 , ' cccc ' , 0 union all select 4 , ' aaaa-1 ' , 1 union all select 5 , ' aaaa-2 ' , 1 union all select 6 , ' bbbb-1 ' , 2 union all select 7 , ' cccc-1 ' , 3 union all select 8 , ' cccc-2 ' , 3 union all select 9 , ' aaaa-1-1 ' , 4 union all select 10 , ' cccc-2-1 ' , 8 union all select 11 , ' cccc-2-2 ' , 8 union all select 12 , ' aaaa-1-2 ' , 4 go -- 创建处理的函数(对原函数做了修改,增加了前缀) -- 处理了最大值id的问题。 alter function f_id() returns @re table (id int ,parentid int , level int ,sid varchar ( 8000 ),pre1 varchar ( 2 ),pre2 varchar ( 2 )) as begin declare @l int set @l = 0 insert @re select id,parentid, @l , right ( 10000 + id, 4 ), '' , '' from tb where parentid = 0 while @@rowcount > 0 begin set @l = @l + 1 insert @re select a.id,a.parentid, @l ,b.sid + ' , ' + right ( 10000 + a.id, 4 ), '' , '' from tb a, @re b where a.parentid = b.id and b. level = @l - 1 end update a set pre2 = case when a. level > 1 then case id when ( select convert ( int , right ( max (sid), 4 )) from @re where parentid = a.parentid and level = a. level ) then ' └ ' else ' ├ ' end else case id when ( select convert ( int , right ( max (sid), 4 )) from @re where level = a. level ) then ' └ ' else ' ├ ' end end from @re a where a. level > 0 update a set pre1 = case when parentid = ( select convert ( int , right ( max (sid), 4 )) from @re where level = a. level - 1 ) then '' else ' │ ' end from @re a where a. level > 1 return end select [ 带缩进的name ] = case when b. [ level ] = 0 then a.name when b. [ level ] = 1 then pre2 + replicate ( ' - ' , 4 ) + a.name else b.pre1 + replicate ( '' ,(b. level - 1 ) * 4 ) + b.pre2 + replicate ( ' - ' , 4 ) + a.name end ,a.name,a.id from tb a,f_id() b where a.id = b.id order by b.sid /**/ /* 结果 带缩进的name name id ---------------------------------------- aaaa aaaa 1 ├----aaaa-1 aaaa-1 4 │ ├----aaaa-1-1 aaaa-1-1 9 │ └----aaaa-1-2 aaaa-1-2 12 ├----aaaa-2 aaaa-2 5 bbbb bbbb 2 ├----bbbb-1 bbbb-1 6 cccc cccc 3 ├----cccc-1 cccc-1 7 └----cccc-2 cccc-2 8 ├----cccc-2-1 cccc-2-1 10 └----cccc-2-2 cccc-2-2 11 */ -- 删除测试 drop table tb drop function f_id