你的东东看不懂?select 'A','C' union select 'A','D' union select 'A','E' union select 'A','H'
--建立函数 create fn_FirstAssm_no( @part_no varchar(20) ) returns varchar(20) as begin declare @Assm_no varchar(20) select @Assm_no=Assm_no from engbomm where part_no=@part_no if @Assm_no is null return @part_no return dbo.fn_FirstAssm_no(@Assm_no) end go--调用查询 select 'A' as Assm_no,part_no from engbomm where dbo.fn_FirstAssm_no(partno)='A'
Assm_no part_no A C A D A E A H 就是得出来的结果,A是最高层,H是最底层,就是最高推算到最底层
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 行)
create table engbomm (Assm_no varchar(8),part_no varchar(8))insert into engbomm 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' union allselect * from engbomm /*测试数据 Assm_no part_no -------- -------- A C C D B F C E E H F Q(所影响的行数为 6 行) */create proc GetDownFromUp(@Pere varchar(8)) as begin create table #tb(flag smallint,PNo varchar(8) primary key) declare @flag smallint set @flag=0 insert into #tb select @flag,part_no from engbomm where Assm_no=@Pere while((select count(1) from engbomm where Assm_no in(select PNo from #tb where flag in(select max(flag) from #tb)))>0) begin select @flag=@flag+1 insert into #tb select @flag,part_no from engbomm where Assm_no in(select PNo from #tb where flag in(select max(flag) from #tb)) if (select count(1) from engbomm where Assm_no in(select PNo from #tb where flag in(select max(flag) from #tb)))=0 begin break end end select @Pere as Assm_no,PNo as part_no from #tb drop table #tb end goexec GetDownFromUp 'A' /*结果 Assm_no part_no -------- -------- A C A D A E A H(所影响的行数为 4 行) */drop proc GetDownFromUp
我用了這樣別的方法 create Procedure FindSa @Project varchar(50) as Insert into PRD_SearSa values(@Project,@Project)insert into PRD_SearSa select @Project,PART_NO from PRD_SearSa as a inner join JDI_ERP.dbo.ENGBOMM as b ON a.Pro_SA=b.ASSM_NO where not(b.PART_NO in (select Pro_Sa from PRD_SearSa))delete from PRD_SearSa where Pro_Sa =@Projectwhile @@rowcount>0 begin insert into PRD_SearSa select @Project,PART_NO from PRD_SearSa as a inner join JDI_ERP.dbo.ENGBOMM as b ON a.Pro_SA=b.ASSM_NO where not(b.PART_NO in (select Pro_Sa from PRD_SearSa)) end GOFindSa '375-10624BN'
union
select 'A','D'
union
select 'A','E'
union
select 'A','H'
create fn_FirstAssm_no(
@part_no varchar(20)
)
returns varchar(20)
as
begin
declare @Assm_no varchar(20)
select @Assm_no=Assm_no from engbomm where part_no=@part_no
if @Assm_no is null
return @part_no
return dbo.fn_FirstAssm_no(@Assm_no)
end
go--调用查询
select 'A' as Assm_no,part_no
from engbomm
where dbo.fn_FirstAssm_no(partno)='A'
A C
A D
A E
A H
就是得出来的结果,A是最高层,H是最底层,就是最高推算到最底层
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 行)
create table engbomm (Assm_no varchar(8),part_no varchar(8))insert into engbomm 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' union allselect * from engbomm
/*测试数据
Assm_no part_no
-------- --------
A C
C D
B F
C E
E H
F Q(所影响的行数为 6 行)
*/create proc GetDownFromUp(@Pere varchar(8))
as
begin
create table #tb(flag smallint,PNo varchar(8) primary key)
declare @flag smallint
set @flag=0
insert into #tb select @flag,part_no from engbomm where Assm_no=@Pere
while((select count(1) from engbomm where Assm_no in(select PNo from #tb where flag in(select max(flag) from #tb)))>0)
begin
select @flag=@flag+1
insert into #tb select @flag,part_no from engbomm where Assm_no in(select PNo from #tb where flag in(select max(flag) from #tb))
if (select count(1) from engbomm where Assm_no in(select PNo from #tb where flag in(select max(flag) from #tb)))=0
begin
break
end
end
select @Pere as Assm_no,PNo as part_no from #tb
drop table #tb
end
goexec GetDownFromUp 'A'
/*结果
Assm_no part_no
-------- --------
A C
A D
A E
A H(所影响的行数为 4 行)
*/drop proc GetDownFromUp
http://blog.csdn.net/roy_88/archive/2006/12/24/1458449.aspx
思想都是一样,都是一级插入一次下级数据,级数也是插入的次数flag.
也可以参考一下老大的车次的那个递归~
create Procedure FindSa
@Project varchar(50)
as
Insert into PRD_SearSa values(@Project,@Project)insert into PRD_SearSa
select @Project,PART_NO from PRD_SearSa as a inner join JDI_ERP.dbo.ENGBOMM as b ON a.Pro_SA=b.ASSM_NO where not(b.PART_NO in (select Pro_Sa from PRD_SearSa))delete from PRD_SearSa where Pro_Sa =@Projectwhile @@rowcount>0
begin
insert into PRD_SearSa
select @Project,PART_NO from PRD_SearSa as a inner join JDI_ERP.dbo.ENGBOMM as b ON a.Pro_SA=b.ASSM_NO where not(b.PART_NO in (select Pro_Sa from PRD_SearSa))
end GOFindSa '375-10624BN'