数据简单,我有2个表,一个BOM1(物料对应关系表),一个是UN_ITEM1(基本资料表),
表BOM1:PARENT ITEM_CODE
20-0001 21-0001
20-0001 21-0002
20-0001 22-1111
20-5555 21-6666
20-5555 21-6667
20-5555 22-6668
20-5555 22-6669
98-2222 97-5858
97-1414 95-5555
......表UN_ITEM1:(ITEM_CODE是唯一的,不会重复)ITEM_CODE COMMONFACTOR(用量) PACKFACTOR(数量)
20-0001 20 5.5
20-5555 25 7
21-0001 5.2 3.5
21-0002 10 8
21-6666 12 6
21-6667 18 9 我要做的是:写一个SQL,在表UN_ITEM1里面批量修改20开头的COMMONFACTOR(用量)为BOM1表里面它相对应的所有21开头的ITEM_CODE的PACKFACTOR(数量)和。
比如我要修改表UN_ITEM1里面20-0001的COMMONFACTOR值,先在BOM1表里面查找到与20-0001所有对应的21开头的ITEM_CODE是:21-0001,21-0002,他们在基本表里面的PACKFACTOR(数量)分别是3.5,8。那么就修改20-0001的COMMONFACTOR值为3.5+8=11.5;以此类推,直至修改完所有20开头的物料。这一点是必要的:数据库环境:sql 2000
表BOM1:PARENT ITEM_CODE
20-0001 21-0001
20-0001 21-0002
20-0001 22-1111
20-5555 21-6666
20-5555 21-6667
20-5555 22-6668
20-5555 22-6669
98-2222 97-5858
97-1414 95-5555
......表UN_ITEM1:(ITEM_CODE是唯一的,不会重复)ITEM_CODE COMMONFACTOR(用量) PACKFACTOR(数量)
20-0001 20 5.5
20-5555 25 7
21-0001 5.2 3.5
21-0002 10 8
21-6666 12 6
21-6667 18 9 我要做的是:写一个SQL,在表UN_ITEM1里面批量修改20开头的COMMONFACTOR(用量)为BOM1表里面它相对应的所有21开头的ITEM_CODE的PACKFACTOR(数量)和。
比如我要修改表UN_ITEM1里面20-0001的COMMONFACTOR值,先在BOM1表里面查找到与20-0001所有对应的21开头的ITEM_CODE是:21-0001,21-0002,他们在基本表里面的PACKFACTOR(数量)分别是3.5,8。那么就修改20-0001的COMMONFACTOR值为3.5+8=11.5;以此类推,直至修改完所有20开头的物料。这一点是必要的:数据库环境:sql 2000
select a.PARENT,sum(isnull(b.PACKFACTOR,0)) as newCOMMONFACTOR
into #newCOMMONFACTOR
from BOM1 a, UN_ITEM1 b
where a.ITEM_CODE=b.ITEM_CODE
and PARENT like '20%' and ITEM_CODE like '21%'
group by a.PARENTupdate UN_ITEM1
set COMMONFACTOR=b.newCOMMONFACTOR
from UN_ITEM1 a,#newCOMMONFACTOR b
where a.ITEM_CODE=b.PARENT
item_code = (select a.item_code from un_item1 b join bom1 a on b.item_code = a.parent
and substring(a.item_code ,0,2) = '21' and substring(parent,0,2)=20)) where item_ code ='20-0001'--是这样嘛?
insert into BOM1(PARENT,ITEM_CODE) values('20-0001','21-0002')
insert into BOM1(PARENT,ITEM_CODE) values('20-0001','22-1111')
insert into BOM1(PARENT,ITEM_CODE) values('20-5555','21-6666')
insert into BOM1(PARENT,ITEM_CODE) values('20-5555','21-6667')
insert into BOM1(PARENT,ITEM_CODE) values('20-5555','22-6668')
insert into BOM1(PARENT,ITEM_CODE) values('20-5555','22-6669')
insert into BOM1(PARENT,ITEM_CODE) values('98-2222','97-5858')
insert into BOM1(PARENT,ITEM_CODE) values('97-1414','95-5555')create table UN_ITEM1(ITEM_CODE varchar(50),COMMONFACTOR float,PACKFACTOR float)insert into UN_ITEM1(ITEM_CODE,COMMONFACTOR,PACKFACTOR) values('20-0001','20','5.5')
insert into UN_ITEM1(ITEM_CODE,COMMONFACTOR,PACKFACTOR) values('20-5555','25','7')
insert into UN_ITEM1(ITEM_CODE,COMMONFACTOR,PACKFACTOR) values('21-0001','5.2','3.5')
insert into UN_ITEM1(ITEM_CODE,COMMONFACTOR,PACKFACTOR) values('21-0002','10','8')
insert into UN_ITEM1(ITEM_CODE,COMMONFACTOR,PACKFACTOR) values('21-6666','12','6')
insert into UN_ITEM1(ITEM_CODE,COMMONFACTOR,PACKFACTOR) values('21-6667','18','9')
select * into #t1 from BOM1
select * into #t2 from UN_ITEM1update a
set a.COMMONFACTOR = sum(c.COMMONFACTOR)
from #t2 a join (select b.ITEM_CODE as CODE,a.ITEM_CODE
from #t2 a join #t1 b on a.ITEM_CODE = b.PARENT
where left(b.ITEM_CODE,2) = '21' and left(a.ITEM_CODE,2) = '20') b
on a.ITEM_CODE = b.ITEM_CODE
join #t2 c on b.CODE = c.ITEM_CODE
create table BOM1(PARENT varchar(10),ITEM_CODE varchar(10))
insert into BOM1
select '20-0001','21-0001' union all
select '20-0001','21-0002' union all
select '20-0001','22-1111' union all
select '20-5555','21-6666' union all
select '20-5555','21-6667' union all
select '20-5555','22-6668' union all
select '20-5555','22-6669' union all
select '98-2222','97-5858' union all
select '97-1414','95-5555'
gocreate table UN_ITEM1(ITEM_CODE varchar(10),COMMONFACTOR decimal(12,2),PACKFACTOR decimal(12,2))
insert into UN_ITEM1
select '20-0001',20,5.5 union all
select '20-5555',25,7 union all
select '21-0001',5.2,3.5 union all
select '21-0002',10,8 union all
select '21-6666',12,6 union all
select '21-6667',18,9
goselect * into #t1 from BOM1
select * into #t2 from UN_ITEM1update a
set a.COMMONFACTOR = b.COMMONFACTOR
from #t2 a join (select a.ITEM_CODE,sum(c.COMMONFACTOR) COMMONFACTOR
from #t2 a join #t1 b on a.ITEM_CODE = b.PARENT
join #t2 c on b.ITEM_CODE = c.ITEM_CODE
where left(b.ITEM_CODE,2) = '21' and left(a.ITEM_CODE,2) = '20'
group by a.ITEM_CODE) b
on a.ITEM_CODE = b.ITEM_CODEselect *
from #t2drop table BOM1,UN_ITEM1,#t1,#t2
/******************ITEM_CODE COMMONFACTOR PACKFACTOR
---------- --------------------------------------- ---------------------------------------
20-0001 15.20 5.50
20-5555 30.00 7.00
21-0001 5.20 3.50
21-0002 10.00 8.00
21-6666 12.00 6.00
21-6667 18.00 9.00(6 行受影响)
--1
select a.PARENT,sum(isnull(b.PACKFACTOR,0)) as newCOMMONFACTOR into #newCOMMONFACTOR from BOM1 a, UN_ITEM1 b where a.ITEM_CODE=b.ITEM_CODE and PARENT like '20%' and a.ITEM_CODE like '21%' group by a.PARENT
--2
update UN_ITEM1 set COMMONFACTOR=b.newCOMMONFACTOR from UN_ITEM1 a,#newCOMMONFACTOR b where a.ITEM_CODE=b.PARENT 20-0001 11.5 5.5
20-5555 15 7
21-0001 5.2 3.5
21-0002 10 8
21-6666 12 6
21-6667 18 9
select * into #t2 from UN_ITEM1可以把#t1、#t2换成你对应的表。
update a set a.COMMONFACTOR = b.PACKFACTOR
from UN_ITEM1 a
join (select a.PARENT,SUM(b.PACKFACTOR) as PACKFACTOR from BOM1 a join UN_ITEM1 b on a.ITEM_CODE = b.ITEM_CODE where a.ITEM_CODE like '21%' group by a.PARENT ) b on a.ITEM_CODE = b.PARENT
where a.ITEM_CODE like '20%'
a
set
a.COMMONFACTOR = b.PACKFACTOR
from
UN_ITEM1 a
join
(select a.PARENT,SUM(b.PACKFACTOR) as PACKFACTOR from BOM1 a join UN_ITEM1 b
on
a.ITEM_CODE = b.ITEM_CODE
where
a.ITEM_CODE like '21%' group by a.PARENT ) b
on
a.ITEM_CODE = b.PARENT
where
a.ITEM_CODE like '20%'