create table tbl (mz varchar(10),lingdao varchar(10))
insert into tbl
select 'A','B' union all
select 'B','C' union all
select 'C','D' union all
select 'E','B' union all
select 'F','C'
go
create function f_test(@mz varchar(10))
returns varchar(3000)
as
begin
declare @str varchar(3000)
set @str=''
while @mz is not null
begin
SET @mz=(SELECT LINGDAO FROM TBL WHERE MZ=@mz)
--set @str=@str+isnull(@mz,'')+','
set @str=@str+case when @mz is null then '' else '、'+@mz end
end
return case when len(@str)>0 then right(@str,len(@str)-1) else '' end
end
go
--生成测试数据
create table BOM(MZ VARCHAR(20),lingdao VARCHAR(20))
insert into BOM select 'A','B'
insert into BOM select 'B','C'
insert into BOM select 'C','D'
insert into BOM select 'E','B'
insert into BOM select 'F','C'
go
--创建用户定义函数
create function f_getParent(@ID VARCHAR(20))
returns @t table(ParentID VARCHAR(20),Level INT)
as
begin
declare @i int
set @i = 0
insert into @t select lingdao,@i from BOM where mz = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t select a.lingdao,@i from BOM a,@t b where a.mz=b.ParentID and b.Level = @i-1
end
return
end
go
--执行查询
select ParentID from dbo.f_getParent('A')
go
--删除测试数据
drop function f_getParent
drop table BOM
go