--测试数据
create table tb(父项 varchar(10),子项 varchar(10))
insert tb select 'a001','A1'
union all select 'a001','D1'
union all select 'a001','E1'
union all select 'A1' ,'B1'
union all select 'A1' ,'C1'
union all select 'E1' ,'C1'
union all select 'E1' ,'D1'
union all select 'E1' ,'F1'
go
--查询处理函数
create function f_id()
returns @re table(子项 varchar(10),[level] int,sid varchar(8000))
as
begin
declare @l int
set @l=1
insert @re select distinct 父项,@l,父项
from tb a
where not exists(select * from tb where 子项=a.父项)
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.子项,@l,b.sid+'>'+a.子项
from tb a,@re b
where a.父项=b.子项 and b.[level]=@l-1
end
return
end
go
--调用函数实现分级显示
select 层号=level,[部件号(产品号)]=子项
from f_id()
order by sid
go