mysql - MySQL汇总和更新

早上好

我有一个MySQL表，它看起来像这样。

表：接收

+--------------+-----------+---------+---------+-------+
| DateReceived | ItemCode  | QtyPack | QtyStan | PCS   |
+--------------+-----------+---------+---------+-------+
| 2016-09-09   | DRY000004 | 30.00   | 0.00    | 24.00 |
| 2016-09-09   | DRY000007 | 60.00   | 0.00    | 12.00 |
| 2016-09-09   | DRY000010 | 90.00   | 0.00    | 1.00  |
| 2016-09-09   | DRY000011 | 100.00  | 0.00    | 1.00  |
| 2016-09-09   | DRY000012 | 1.00    | 0.00    | 12.00 |
| 2016-09-09   | DRY000012 | 1.00    | 0.00    | 12.00 |
+--------------+-----------+---------+---------+-------+

和表差异

+-----------+-----------+
| ItemCode  | Receiving |
+-----------+-----------+
| DRY000004 |      NULL |
| DRY000007 |      NULL |
| DRY000010 |      NULL |
| DRY000011 |      NULL |
| DRY000012 |      NULL |
| DRY000013 |      NULL |
| DRY000014 |      NULL |
| DRY000016 |      NULL |
| DRY000017 |      NULL |
| DRY000018 |      NULL |
| DRY000019 |      NULL |
| DRY000020 |      NULL |
| DRY000021 |      NULL |
| EQU000001 |      NULL |
+-----------+-----------+

我的目标是在将QtyPack * PCS + QtyStan的总和传输到表的Receiving列中之前：Variance，这是我的代码

UPDATE Variance VAR INNER JOIN receiving REv ON VAR.ItemCode = REV.ItemCode
SET VAR.Receiving = replace(REV.QtyPack, ',', '') * replace(REV.PCS, ',', '') + replace(REV.QtyStan, ',', '')
WHERE VAR.ItemCode = REV.ItemCode and REV.Status = 'Posted' AND rev.DateReceived between '2016-09-09' and
'2016-09-09';

请忽略where子句中的Criteria，但是如果您希望这样做，这是Table:Receiving中我的其他条件

即使代码正常工作，也似乎出现了问题，我在接收中有一个具有相同ItemCode的数据，并且该数据是DRY000012且QtyPack为1

简而言之，这是我的问题

如何在将所有ItemCode汇总到表Variance中之前将其总计？例如，DRY000012在接收前必须变为24。

TYSM为将来提供帮助

最佳答案

UPDATE Variance t1
INNER JOIN
(
    SELECT ItemCode,
           Status,
           DateReceived,
           REPLACE(QtyPack, ',', '') * REPLACE(PCS, ',', '') +
           REPLACE(QtyStan, ',', '') AS amount
    FROM Receiving
) t2
    ON t1.ItemCode = t2.ItemCode
SET t1.Receiving = t2.amount
WHERE t2.Status = 'Posted' AND
      t2.DateReceived BETWEEN '2016-09-09' AND '2016-09-09'

假设ItemCode在Receiving表中仅出现一次，并且在执行UPDATE之前我们不需要进行任何类型的聚合。

itemCode

mysql - MySQL汇总和更新