有一表a,记录树型类别结构如下:
id ClassName ParentId
===========================
1 Class1 0
2 Class2 1
3 Class3 2
4 Class4 0
5 Class5 4
注意树的层数不定
要使得到如下结果,怎么写SQLid ClassNameList
===========================
3 Class1|Class2|Class3
5 Class4|Class5
id ClassName ParentId
===========================
1 Class1 0
2 Class2 1
3 Class3 2
4 Class4 0
5 Class5 4
注意树的层数不定
要使得到如下结果,怎么写SQLid ClassNameList
===========================
3 Class1|Class2|Class3
5 Class4|Class5
insert into tree values( 1,'class1' ,0)
insert into tree values( 2,'class2' ,1)
insert into tree values( 3,'class3' ,2)
insert into tree values( 4,'class4' ,0)
insert into tree values( 5,'class5' ,4)
go
create proc tree_proc ( @id int)
as
if (select parentid from tree where id = @id) = 0
print '空'
else
declare @id_next int , @classname varchar(50) ,@out varchar(50)
set @id_next = @id
select @out = classname from tree where id = @id
while (select parentid from tree where id = @id_next) > 0
beginselect @id_next = parentid from tree where id = @id_next
select @classname = classname from tree where id = @id_next
set @out = @out + '|' + @classname
end
select @id , @out
结果2 class2|class1