--示例数据
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
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 ParentID=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.ParentID=b.id and b.level=@l-1
end
return
end
go
--调用函数实现查询
select a.*,带缩进的Name=space(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