34,596
社区成员
发帖
与我相关
我的任务
分享
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
...
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 G
98-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的省略
SO_NUMBER CUSTOMER PARENT(成品) ITEM_CODE(半成品或物料) BOM_UNIT
SO10-5257 HALLM-01 99-88551-01030 #8003042-01010 G
SO10-5257 HALLM-01 99-88551-01030 #6300035-01010 G
SO10-5257 HALLM-01 99-88551-01030 21-00001-00010 G
SO10-5257 HALLM-01 99-88551-01030 22-00424-01010 G
SO10-5341 HALLM-02 98-78496-SP417K #98-78496-SP417K G
SO10-5341 HALLM-02 98-78496-SP417K #7678496-00010 G
SO10-5341 HALLM-02 98-78496-SP417K #88-78496-SP2027 G
SO10-5341 HALLM-02 98-78496-SP417K #87-78496-SP2027 G
SO10-5341 HALLM-02 98-78496-SP417K 87-02029-00020 G
SO10-5341 HALLM-02 98-78496-SP417K 02-00002-01011 G
create table tb1(SO_NUMBER varchar(50),CUSTOMER varchar(50),ITEM_CODE varchar(50))
set nocount on
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')
go
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')
set nocount off
go
create function get_proC(@PARENT varchar(50))
returns varchar(1000)
as
begin
declare @t table(PARENT varchar(50),ITEM_CODE varchar(50),[level] int)
declare @i int
declare @ret varchar(1000)
declare @j int
set @j=1
set @i=1
insert into @t
select PARENT,ITEM_CODE,@i from BOM1 where PARENT=@PARENT
while @@rowcount>0
begin
set @i=@i+1
insert into @t
select a.PARENT,a.ITEM_CODE,@i from BOM1 a join @t b on a.PARENT=b.ITEM_CODE
where b.level=@i-1
end
select @j = max([level]) from @t
select @ret = isnull(@ret+',','')+ITEM_CODE
from @t t
where not exists (select 1 from @t where PARENT = t.ITEM_CODE)
return @ret
end
go
select a.SO_NUMBER,a.CUSTOMER,a.ITEM_CODE,
b.ITEM_CODE,b.BOM_UNIT
from tb1 a join BOM1 b on charindex(','+b.ITEM_CODE+',',','+dbo.get_proC(a.ITEM_CODE)+',') > 0
--这个地方有必要就left join!
drop function get_proC
drop table tb1,BOM1
/**********************
SO_NUMBER CUSTOMER ITEM_CODE ITEM_CODE BOM_UNIT
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
SO10-5257 HALLM-01 99-88551-01030 #8003042-01010 G
SO10-5257 HALLM-01 99-88551-01030 #6300035-01010 G
SO10-5257 HALLM-01 99-88551-01030 21-00001-00010 G
SO10-5257 HALLM-01 99-88551-01030 22-00424-01010 G
SO10-5341 HALLM-02 98-78496-SP417K #98-78496-SP417K G
SO10-5341 HALLM-02 98-78496-SP417K #7678496-00010 G
SO10-5341 HALLM-02 98-78496-SP417K #88-78496-SP2027 G
SO10-5341 HALLM-02 98-78496-SP417K #87-78496-SP2027 G
SO10-5341 HALLM-02 98-78496-SP417K 87-02029-00020 G
SO10-5341 HALLM-02 98-78496-SP417K 02-00002-01011 G
(10 行受影响)
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')