修改一下递归SQL

Question

有如下表tb1,有3个字段SO_NUMBER       CUSTOMER      ITEM_CODE(实际是成品)
SO10-5257       HALLM-01      99-88551-01030
SO10-5341       HALLM-02      98-78496-SP417K
SO30-0524       HALLM-03      97-94730-02010另外有表BOM1：其中PARENT与ITEM_CODE可以存在不定的多层的递归关系，层层递归，99下面是98，98下是97,97下是94,94下是91等等。只要有下层，就继续拆分到最低层，比如成品99-88551-01030，拆到最低就是21-00001-00010，22-00424-01010一共有2个.目的就是找到最底层的物料。PARENT(成品)            ITEM_CODE(半成品或物料)        BOM_UNIT
99-88551-01030           97-88551-01030                   PCS
97-88551-01030           96-88551-01010                   PCS
96-88551-01010           #8003042-01010                   G
96-88551-01010           #6300035-01010                   G
96-88551-01010           20-88551-01030                   PCS
20-88551-01030           21-00001-00010                   G
20-88551-01030           22-00424-01010                   G98-78496-SP417K          #98-78496-SP417K                 G
98-78496-SP417K          97-78496-SP417K                  PCS
97-78496-SP417K          96-78496-SP2027                  PCS
97-78496-SP417K          #7678496-00010                   G
97-78496-SP417K          #88-78496-SP2027                 G
96-78496-SP2027          94-78496-SP2027                  PCS
96-78496-SP2027          #87-78496-SP2027                 G
94-78496-SP2027          90-78496-01010                   PCS
90-78496-01010           87-02029-00020                   G
90-78496-01010           02-00002-01011                   G

SO30-0524的省略我想要的效果，查找tb1中成品-物料对应关系：SO_NUMBER         CUSTOMER       PARENT(成品)          ITEM_CODE(半成品或物料)        BOM_UNIT
SO10-5257       HALLM-01        99-88551-01030          21-00001-00010                 G
SO10-5257       HALLM-01        99-88551-01030          22-00424-01010                 GSO10-5341       HALLM-02        98-78496-SP417K         87-02029-00020                 G
SO10-5341       HALLM-02        98-78496-SP417K         02-00002-01011                 G数据库环境：sql 2000
在以下的存储过程基础上修改：

Answer 1

create proc proc_1
asselect distinct a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,b.ITEM_CODE CODE,b.BOM_UNIT,1 as Level
into #t
from tb1 a join BOM1 b on a.ITEM_CODE = b.PARENTdeclare @Level int
set @Level = 1
while exists (
    select 1 
    from #t a inner join BOM1 b on a.CODE = b.PARENT
    where a.Level = @Level
    )
begin    
    insert #t
    select distinct a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,isnull(b.ITEM_CODE,a.CODE) CODE,isnull(b.BOM_UNIT,a.BOM_UNIT) as BOM_UNIT,a.Level + 1 as Level
    from #t a left join BOM1 b on a.CODE = b.PARENT
    where a.Level = @Level
    set @Level = @Level     + 1
endselect 
  a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,a.CODE,a.BOM_UNIT
  -- 其他表字段
from #t a
--,tb1 b   --其他表   
where a.Level = @Level
--and  ... 连接条件
order by a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,a.CODEdrop table #tgo原帖：http://topic.csdn.net/u/20110825/17/24dd0ac4-962e-43f8-9fbc-5c7404c6e2a9.html

Answer 2

本贴的要求与原帖略有不同，本贴只需要拆分到最下层，其他中间物料不用管。原帖就是要拆分所有中间物料。
测试数据：create table tb1(SO_NUMBER varchar(50),CUSTOMER varchar(50),ITEM_CODE varchar(50))insert into tb1(SO_NUMBER,CUSTOMER,ITEM_CODE) 
values('SO10-5257','HALLM-01','99-88551-01030')
insert into tb1(SO_NUMBER,CUSTOMER,ITEM_CODE) 
values('SO10-5341','HALLM-02','98-78496-SP417K')
insert into tb1(SO_NUMBER,CUSTOMER,ITEM_CODE) 
values('SO30-0524','HALLM-03','97-94730-02010')
---------------------------------------create table BOM1(PARENT varchar(50),ITEM_CODE varchar(50),BOM_UNIT varchar(50))insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('99-88551-01030','97-88551-01030','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-88551-01030','96-88551-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','#8003042-01010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','#6300035-01010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','20-88551-01030','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20-88551-01030','21-00001-00010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20-88551-01030','22-00424-01010','G')insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98-78496-SP417K','#98-78496-SP417K','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98-78496-SP417K','97-78496-SP417K','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','96-78496-SP2027','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','#7678496-00010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','#88-78496-SP2027','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-78496-SP2027','94-78496-SP2027','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-78496-SP2027','#87-78496-SP2027','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('94-78496-SP2027','90-78496-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90-78496-01010','87-02029-00020','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90-78496-01010','02-00002-01011','G')

Answer 3

create proc proc_1
asselect distinct a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,b.ITEM_CODE CODE,b.BOM_UNIT,1 as Level
into #t
from tb1 a join BOM1 b on a.ITEM_CODE = b.PARENTdeclare @Level int
set @Level = 1
while exists (
    select 1 
    from #t a inner join BOM1 b on a.CODE = b.PARENT
    where a.Level = @Level
    )
begin    
    insert #t
    select distinct a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,isnull(b.ITEM_CODE,a.CODE) CODE,isnull(b.BOM_UNIT,a.BOM_UNIT) as BOM_UNIT,a.Level + 1 as Level
    from #t a left join BOM1 b on a.CODE = b.PARENT
    where a.Level = @Level
    set @Level = @Level + 1
endselect 
  a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,a.CODE,a.BOM_UNIT
  -- 其他表字段
from #t a
--,tb1 b   --其他表   
where a.Level = (select max(Level) from #t where ITEM_CODE = a.ITEM_CODE)
--and  ... 连接条件
order by a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,a.CODEdrop table #tgo

Answer 4

create proc proc_1
asselect distinct a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,b.ITEM_CODE CODE,b.BOM_UNIT,1 as Level
into #t
from tb1 a join BOM1 b on a.ITEM_CODE = b.PARENT   --left join 改为 joindeclare @Level int
set @Level = 1
while exists (
    select 1 
    from #t a inner join BOM1 b on a.CODE = b.PARENT
    where a.Level = @Level
    )
begin    
    insert #t
    select distinct a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,isnull(b.ITEM_CODE,a.CODE) CODE,isnull(b.BOM_UNIT,a.BOM_UNIT) as BOM_UNIT,a.Level + 1 as Level
    from #t a join BOM1 b on a.CODE = b.PARENT
    where a.Level = @Level
    set @Level = @Level + 1
endselect 
  a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,a.CODE,a.BOM_UNIT,a.level
  -- 其他表字段
from #t a
--,tb1 b   --其他表   
where a.Level = (select max(Level) from #t where ITEM_CODE = a.ITEM_CODE)  --条件选每个的level最大值
--and  ... 连接条件
order by a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,a.CODEdrop table #tgo

Answer 5

5楼有测试，结果如下：
create table tb1(SO_NUMBER varchar(50),CUSTOMER varchar(50),ITEM_CODE varchar(50))
insert into tb1(SO_NUMBER,CUSTOMER,ITEM_CODE) 
values('SO10-5257','HALLM-01','99-88551-01030')
insert into tb1(SO_NUMBER,CUSTOMER,ITEM_CODE) 
values('SO10-5341','HALLM-02','98-78496-SP417K')
insert into tb1(SO_NUMBER,CUSTOMER,ITEM_CODE) 
values('SO30-0524','HALLM-03','97-94730-02010')
gocreate table BOM1(PARENT varchar(50),ITEM_CODE varchar(50),BOM_UNIT varchar(50))
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('99-88551-01030','97-88551-01030','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-88551-01030','96-88551-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','#8003042-01010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','#6300035-01010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-88551-01010','20-88551-01030','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20-88551-01030','21-00001-00010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('20-88551-01030','22-00424-01010','G')insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98-78496-SP417K','#98-78496-SP417K','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('98-78496-SP417K','97-78496-SP417K','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','96-78496-SP2027','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','#7678496-00010','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('97-78496-SP417K','#88-78496-SP2027','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-78496-SP2027','94-78496-SP2027','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('96-78496-SP2027','#87-78496-SP2027','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('94-78496-SP2027','90-78496-01010','PCS')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90-78496-01010','87-02029-00020','G')
insert into BOM1(PARENT,ITEM_CODE,BOM_UNIT) 
values('90-78496-01010','02-00002-01011','G')
gocreate proc proc_1
asselect distinct a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,b.ITEM_CODE CODE,b.BOM_UNIT,1 as Level
into #t
from tb1 a join BOM1 b on a.ITEM_CODE = b.PARENTdeclare @Level int
set @Level = 1
while exists (
    select 1 
    from #t a inner join BOM1 b on a.CODE = b.PARENT
    where a.Level = @Level
    )
begin    
    insert #t
    select distinct a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,isnull(b.ITEM_CODE,a.CODE) CODE,isnull(b.BOM_UNIT,a.BOM_UNIT) as BOM_UNIT,a.Level + 1 as Level
    from #t a join BOM1 b on a.CODE = b.PARENT
    where a.Level = @Level
    set @Level = @Level + 1
endselect 
  a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,a.CODE,a.BOM_UNIT,a.level
  -- 其他表字段
from #t a
--,tb1 b   --其他表   
where a.Level = (select max(Level) from #t where ITEM_CODE = a.ITEM_CODE)
--and  ... 连接条件
order by a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,a.CODEdrop table #tgoexec proc_1drop proc proc_1
drop table tb1,BOM1
/***************************SO_NUMBER                                          CUSTOMER                                           ITEM_CODE                                          CODE                                               BOM_UNIT                                           level
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------
SO10-5257                                          HALLM-01                                           99-88551-01030                                     21-00001-00010                                     G                                                  4
SO10-5257                                          HALLM-01                                           99-88551-01030                                     22-00424-01010                                     G                                                  4
SO10-5341                                          HALLM-02                                           98-78496-SP417K                                    02-00002-01011                                     G                                                  5
SO10-5341                                          HALLM-02                                           98-78496-SP417K                                    87-02029-00020                                     G                                                  5(4 行受影响)

Answer 6

把left join改成inner join 就可以了