mysql - 修改 SQL 表以压缩相似的行，同时对列进行求和

Question

这是一个问题，这是我想做的事情的一般要点:

Sum values from multiple rows into one row

但是，据我所知，我正在寻求进一步的功能，这将永久修改相关表，使其看起来像其他线程中建议的 SELECT 语句的结果。

所以表格:

Sales
--------------------------------------
account    product    qty    amount
--------------------------------------
01010      bottle     10     200
01010      bottle     20     100
01010      bottle     5      10
11111      can        50     200
11111      can        25     150

...将被永久修改为如下所示

Sales
--------------------------------------
account    product    qty    amount
--------------------------------------
01010      bottle     35     310
11111      can        75     350

正如链接中的回答，使用带有 SUM 和 GROUP BY 的 SELECT 可以向我展示表格需要的样子，但如何实际将这些更改应用到销售表？

编辑:每次将新批处理的销售添加到系统中时都会运行此查询。它的目的是在添加新记录后清理销售表。

替代方法

销售中的新记录使用如下方式从不同的表插入:

"INSERT INTO sales
    SELECT account, product, qty, amount
    FROM new_sales;"

如果有一种方法可以在之前的 INSERT 过程中处理求和，而不是首先添加重复的行，那也是可以接受的。请记住，此解决方案仍然需要适用于销售中不存在重复行的新记录。销售。

编辑:为了后代

一般 react 似乎是我最初的方法是不可能的 - 缺少使用 CREATE 和 SELECT 创建一个 temp_sales 表，然后完全清除 sales，然后将 temp_sales 的内容复制到已清除的 sales 表中，并截断 temp_sales 以供将来使用。

已接受的解决方案使用了我也提到过的“替代方法”。

Answer 1

假设 new_sales 在销售更新后被截断，然后开始重新填充，您可以使用 insert..on 重复键..update 例如

MariaDB [sandbox]> drop table if exists t,t1;
Query OK, 0 rows affected (0.20 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> create table t
    -> (account varchar(5),    product varchar(20),    qty  int default 0,  amount int default 0);
Query OK, 0 rows affected (0.16 sec)

MariaDB [sandbox]> create table t1
    -> (account varchar(5),    product varchar(20),    qty  int default 0,  amount int default 0);
Query OK, 0 rows affected (0.24 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> alter table t
    -> add unique key k1(account,product);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> truncate table t1;
Query OK, 0 rows affected (0.23 sec)

MariaDB [sandbox]> insert into t1 values
    -> ('01010'  ,    'bottle'   ,  10   ,  200),
    -> ('01010'  ,    'bottle'   ,  20   ,  100),
    -> ('01010'  ,    'bottle'   ,  5    ,  10),
    -> ('11111'  ,    'can'      ,  50   ,  200),
    -> ('11111'  ,    'can'      ,  25   ,  150);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> truncate table t;
Query OK, 0 rows affected (0.28 sec)

MariaDB [sandbox]> insert into t
    -> select account,product,t1qty,t1amount
    -> from
    -> (
    -> select t1.account,t1.product,sum(t1.qty) t1qty,sum(t1.amount) t1amount from t1 group by t1.account,t1.product
    -> ) s
    -> on duplicate key
    -> update qty = t.qty + t1qty, amount = t.amount + t1amount;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> truncate table t1;
Query OK, 0 rows affected (0.32 sec)

MariaDB [sandbox]> insert into t1 values
    -> ('01010'  ,    'bottle'   ,  10   ,  200),
    -> ('01011'  ,    'bottle'   ,  20   ,  100),
    -> ('01011'  ,    'bottle'   ,  5    ,  10),
    -> ('11111'  ,    'can'      ,  50   ,  200),
    -> ('11111'  ,    'can'      ,  25   ,  150);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> insert into t
    -> select account,product,t1qty,t1amount
    -> from
    -> (
    -> select t1.account,t1.product,sum(t1.qty) t1qty,sum(t1.amount) t1amount from t1 group by t1.account,t1.product
    -> ) s
    -> on duplicate key
    -> update qty = t.qty + t1qty, amount = t.amount + t1amount;
Query OK, 5 rows affected (0.02 sec)
Records: 3  Duplicates: 2  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]>
MariaDB [sandbox]> select * from t;
+---------+---------+------+--------+
| account | product | qty  | amount |
+---------+---------+------+--------+
| 01010   | bottle  |   45 |    510 |
| 11111   | can     |  150 |    700 |
| 01011   | bottle  |   25 |    110 |
+---------+---------+------+--------+
3 rows in set (0.00 sec)

MariaDB [sandbox]>