时间戳

首页 > 解决方案 > MySQL关于重复键更新从查询中添加值

mysql - MySQL关于重复键更新从查询中添加值

问题描述

我想要：

谢谢您的帮助！

delete from 02_inventory.tempinvsummary;

insert into 02_inventory.tempinvsummary 
(select pn,
sum(if(tp='Coil',no,0)) as 'coil',
sum(if(tp='Blank',no,0)) as 'blank', 
sum(if(tp='Press',no,0)) as 'press',
sum(if(tp='Assy',no,0)) as 'assy' ,
sum(if(tp='Robot',no,0)) as 'robot'
from inventory  
group by pn);

insert into 02_inventory.tempinvsummary 
(select pn,
sum(if(tp='Coil',no,0)) as 'suplcoil',
sum(if(tp='Blank',no,0)) as 'suplblank', 
sum(if(tp='Press',no,0)) as 'suplpress',
sum(if(tp='Assy',no,0)) as 'suplassy' ,
sum(if(tp='Robot',no,0)) as 'suplrobot'
from suplinventory
group by pn)
on duplicate key update

'====================
'prbly need codes here
coil=coil+suplcoil ????
'====================    
select * from 00_masterdata.itemmaster, 02_inventory.tempinvsummary
where 00_masterdata.itemmaster.pn= 02_inventory.tempinvsummary.pn;

标签： mysqlsqlduplicatesmariadb

解决方案

假设你的桌子看起来有点像这样

drop table if exists tempinvsummary, inventory, suplinventory;

create table tempinvsummary (pn int primary key, col1 int);
create table inventory (pn int , col1 int);
create table suplinventory (pn int , col1 int);

insert into tempinvsummary values (1,null),(2,null);

insert into inventory values (1,10),(1,10),(3,10);
insert into suplinventory values (2,10);

您可以合并并汇总这两个表，然后像这样测试和更新重复项

insert into tempinvsummary (pn,col1)
select  pn,
        sum(case when src = 'i' then col1 else 0 end) + sum(case when src ='s' then col1 else 0 end) netcol1
from
(
select 'i' as src,i.pn,i.col1 from inventory i
union all
select 's' as src,s.pn,s.col1 from suplinventory s
) s
group by pn
on duplicate key
update col1 = values(col1);

结果

+----+------+
| pn | col1 |
+----+------+
|  1 |   20 |
|  2 |   10 |
|  3 |   10 |
+----+------+
3 rows in set (0.00 sec)

但我不相信你需要一个临时表，如果你只需要它来加入 00_masterdata.itemmaster

例如

select  masterdata.username,pn,netcol1
from    users as masterdata
join
(
select  pn,
        sum(case when src = 'i' then col1 else 0 end) + sum(case when src ='s' then col1 else 0 end) netcol1
from
(
select 'i' as src,i.pn,i.col1 from inventory i
union all
select 's' as src,s.pn,s.col1 from suplinventory s
) s
group by pn

) a
on a.pn = masterdata.id;

我在我的数据库中使用一个名为 users 的方便表来说明这一点

结果

+----------+------+---------+
| username | pn   | netcol1 |
+----------+------+---------+
| John     |    1 |      20 |
| Jane     |    2 |      10 |
| Ali      |    3 |      10 |
+----------+------+---------+
3 rows in set (0.01 sec)

推荐阅读