--
生成测试数据
create table BOM(ID int ,parentID int ,sClassName varchar ( 10 ))
insert into BOM values ( 1 , 0 , ' 1111 ' )
insert into BOM values ( 2 , 1 , ' 1111_1 ' )
insert into BOM values ( 3 , 2 , ' 1111-1-1 ' )
insert into BOM values ( 4 , 3 , ' 1111-1-1-1 ' )
insert into BOM values ( 5 , 1 , ' 1111-2 ' )
go
-- 创建用户定义函数,每个子节点de父节点的信息
create function f_getParent( @ID int )
returns varchar ( 40 )
as
begin
declare @ret varchar ( 40 )
while exists ( select 1 from BOM where ID = @ID and parentID <> 0 )
begin
select @ID = b.ID, @ret = ' , ' + rtrim (b.ID) + isnull ( @ret , '' )
from
BOM a,BOM b
where
a.ID = @ID and b.ID = a.parentID
end
set @ret = stuff ( @ret , 1 , 1 , '' )
return @ret
end
go
-- 执行查询
select ID, isnull (dbo.f_getParent(ID), '' ) as parentID from BOM
go
-- 输出结果
/*
ID parentID
----------- ----------------------------------------
1
2 1
3 1,2
4 1,2,3
5 1
*/
-- 删除测试数据
drop function f_getParent
drop table BOM
create table BOM(ID int ,parentID int ,sClassName varchar ( 10 ))
insert into BOM values ( 1 , 0 , ' 1111 ' )
insert into BOM values ( 2 , 1 , ' 1111_1 ' )
insert into BOM values ( 3 , 2 , ' 1111-1-1 ' )
insert into BOM values ( 4 , 3 , ' 1111-1-1-1 ' )
insert into BOM values ( 5 , 1 , ' 1111-2 ' )
go
-- 创建用户定义函数,每个子节点de父节点的信息
create function f_getParent( @ID int )
returns varchar ( 40 )
as
begin
declare @ret varchar ( 40 )
while exists ( select 1 from BOM where ID = @ID and parentID <> 0 )
begin
select @ID = b.ID, @ret = ' , ' + rtrim (b.ID) + isnull ( @ret , '' )
from
BOM a,BOM b
where
a.ID = @ID and b.ID = a.parentID
end
set @ret = stuff ( @ret , 1 , 1 , '' )
return @ret
end
go
-- 执行查询
select ID, isnull (dbo.f_getParent(ID), '' ) as parentID from BOM
go
-- 输出结果
/*
ID parentID
----------- ----------------------------------------
1
2 1
3 1,2
4 1,2,3
5 1
*/
-- 删除测试数据
drop function f_getParent
drop table BOM
go
本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2010/01/04/1639181.html，如需转载请自行联系原作者
