create table ta ( Assm_no varchar(2), part_no varchar(2))
insert ta
select 'A', 'C'union all
select 'C', 'D'union all
select 'B', 'F'union all
select 'C', 'E'union all
select 'E', 'H'union all
select 'F', 'Q'
用存储过程实现:
create proc test_p @Assm_no varchar(5)
as
begin
declare @i int
set @i=0
select * ,级数=@i into #
from ta where Assm_no=@Assm_no
while @@rowcount>0
begin
set @i=@i+1
insert #
select a.*,级数=@i
from ta a,# b
where b.part_no=a.Assm_no
and b.级数=@i-1
end
select Assm_no=(select Assm_no from # where 级数=(select min(级数) from # )),part_no
from # a order by part_no
end
测试:
exec test_p 'A'
(所影响的行数为 1 行)
(所影响的行数为 2 行)
(所影响的行数为 1 行)
(所影响的行数为 0 行)
Assm_no part_no
------- -------
A C
A D
A E
A H
(所影响的行数为 4 行)