从另一个表更新sum - Update sum from another table

我有物品移动到和从库存表和报告表。

动作表:<>之前------------------------------------Id | part_number | amount | code |------------------------------------101 | 3-80-75 | 55 | 2102 | 5-88-70米| -65 | 1103 | 1-46-57 | 11 | 1104 | 3-80-75 | -8 | 2105 | 5-51-54 | 76 | 2106 | 3-80-75 | -22 |107 | 5-51-54 | 62 | 1108 | 1-46-57 | 3 | 2109 | 3-80-75 | -16 |-----------------------------------之前

报告表:<>之前----------------------------------Id | part_number | total_code2 |----------------------------------456 | 3-80-75 |457 | 5-88-70米|[1-46-57]459 | 3-80-75 |460 | 5-51-54 |----------------------------------之前

我只需要用代码2的总和来更新报表，对于每个零件编号。

结果应该是这样的:

<>之前----------------------------------Id | part_number | total_code2 |----------------------------------456 | 3-80-75 | 31457 | 5-88-70米| 0458 | 1-46-57 | 3459 | 3-80-75 | -24460 | 5-51-54 | 76----------------------------------

据我所知，您希望对具有code = 2的数字的代码求和。您可以使用一个sql查询和内部连接来实现:

UPDATE report r
JOIN
    (
        SELECT part_number, SUM(code) total_code
        FROM movements
        WHERE code = 2 
    ) m
ON m.part_number = r.part_number
SET total_code2 = m.total_code

问题中所示的报表表有至少一个部分的两行

所以我在报表中添加了一个代码列。

仅根据代码2更新了3行。

在允许问题混乱之后匹配预期结果，imho

DDL:

create table movements
(   id int not null,
    part_number varchar(40) not null,
    amount int not null,
    code int not null
);
-- truncate table movements;
insert movements (id,part_number,amount,code) values (101,'3-80-75',55,2);
insert movements (id,part_number,amount,code) values (102,'5-88-70M',-65,1);
insert movements (id,part_number,amount,code) values (103,'1-46-57',11,1);
insert movements (id,part_number,amount,code) values (104,'3-80-75',-8,2);   
insert movements (id,part_number,amount,code) values (105,'5-51-54',76,2);
insert movements (id,part_number,amount,code) values (106,'3-80-75',-22,1);
insert movements (id,part_number,amount,code) values (107,'5-51-54',62,1);
insert movements (id,part_number,amount,code) values (108,'1-46-57',-3,2);
insert movements (id,part_number,amount,code) values (109,'3-80-75',-16,2);
create table report
(   id int not null,
    part_number varchar(40) not null,
    code int not null,
    total_code2 int not null
);
-- truncate table report;
insert report (id,part_number,code,total_code2) values (456,'3-80-75',2,0);
insert report (id,part_number,code,total_code2) values (458,'1-46-57',2,0);
insert report (id,part_number,code,total_code2) values (460,'5-51-54',2,0);

更新:

UPDATE report r
JOIN
    (   SELECT part_number, SUM(amount) total_code
        FROM movements
        WHERE code = 2 
        group by part_number
    ) m
ON m.part_number = r.part_number
SET total_code2 = m.total_code
where r.code=2  --   <----- only update code 2

结果:

select * from report;
+-----+-------------+------+-------------+
| id  | part_number | code | total_code2 |
+-----+-------------+------+-------------+
| 456 | 3-80-75     |    2 |          31 |
| 458 | 1-46-57     |    2 |          -3 |
| 460 | 5-51-54     |    2 |          76 |
+-----+-------------+------+-------------+
3 rows in set (0.00 sec)