--建立测试环境
Create Table 表(料件編號 varchar(10),子料件 varchar(10))
--插入数据
insert into 表
select '100-A-1','500-A-C' union
select '100-A-1','500-A-B' union
select '100-A-1','500-A-D' union
select '500-A-C','600-A-F' union
select '500-A-C','600-A-D' union
select '500-A-B','600-D-D' union
select '500-A-B','600-D-7' union
select '500-A-D','700-F-1' union
select '500-A-D','700-F-4'
--select * from 表
--测试语句
go
create function f_cid(@子料件 varchar(10))
returns @re table(父项 varchar(10),[level] int,sid varchar(8000))
as
begin
declare @l int
set @l=1
insert @re select 料件編號,@l,料件編號
from 表 a
where 子料件=@子料件
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.料件編號,@l,a.料件編號+' -> '+b.sid
from 表 a,@re b
where a.子料件=b.父项 and b.[level]=@l-1
end
update @re set level=@l-level
return
end
go
select * from dbo.f_cid('700-F-4')
go
--建立测试环境
Create Table 表(料件編號 varchar(10),子料件 varchar(10))
--插入数据
insert into 表
select '100-A-1','500-A-C' union
select '100-A-1','500-A-B' union
select '100-A-1','500-A-D' union
select '500-A-C','600-A-F' union
select '500-A-C','600-A-D' union
select '500-A-B','600-D-D' union
select '500-A-B','600-D-7' union
select '500-A-D','700-F-1' union
select '500-A-D','700-F-4'
--select * from 表
--测试语句
go
create function f_cid(@子料件 varchar(10))
returns @re table(父项 varchar(10),[level] int,sid varchar(8000))
as
begin
declare @l int
set @l=1
insert @re select 料件編號,@l,料件編號
from 表 a
where 子料件=@子料件
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.料件編號,@l,b.sid+'<'+a.料件編號
from 表 a,@re b
where a.子料件=b.父项 and b.[level]=@l-1
end
return
end
go
select * from dbo.f_cid('700-F-4')
go
--建立测试环境
Create Table 表(料件編號 varchar(10),子料件 varchar(10))
--插入数据
insert into 表
select '100-A-1','500-A-C' union
select '100-A-1','500-A-B' union
select '100-A-1','500-A-D' union
select '500-A-C','600-A-F' union
select '500-A-C','600-A-D' union
select '500-A-B','600-D-D' union
select '500-A-B','600-D-7' union
select '500-A-D','700-F-1' union
select '500-A-D','700-F-4'
--select * from 表
--测试语句
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 表 a
where not exists(select * from 表 where 料件編號=a.子料件)
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.料件編號,@l,b.sid+'<'+a.料件編號
from 表 a,@re b
where a.子料件=b.父项 and b.[level]=@l-1
end
return
end
go
select 父项 ,level from dbo.f_id() where sid like'600-A-D%'