修改sql表以压缩相似的行，同时对列求和

omqzjyyz 于 2021-06-25 发布在 Mysql

关注(0)|答案(4)|浏览(295)

这里有一个问题是我要做的事情的基本要点：
将多行中的值相加为一行
然而，据我所知，我正在寻求进一步的功能，这将永久性地修改有问题的表，使其看起来像在另一个线程中建议的select语句的结果。
所以这张table：

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

正如链接中所回答的，使用select with sum和group by可以显示表需要的外观，但是如何将这些更改实际应用到sales表？
编辑：每次向系统中添加新的销售批次时，都将运行此查询。它的目的是在添加新记录后清理sales表。
替代方法
sales中的新记录是从另一个表插入的，如下所示：

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

如果有办法在上一次插入期间处理求和，而不是首先添加重复的行，那也是可以接受的。请记住，对于sales中没有重复行的新记录，仍然需要使用此解决方案。
编辑：为子孙后代
一般的回答似乎是，我最初的方法是不可能的——除了创建一个带有create和select的temp\u sales表，然后完全清除sales，然后将temp\u sales的内容复制到清除的sales表中，并截断temp\u sales以备将来使用。
公认的解决方案使用了我也提到过的“替代方法”。

sql mysql Database sum sql-update

来源：https://stackoverflow.com/questions/48249204/modify-a-sql-table-to-condense-similar-rows-while-summing-up-a-column

4条答案

按热度按时间

prdp8dxp1#

旧table

CREATE TABLE yourtable (
  [state] varchar(2),
  [month] varchar(7),
  [ID] int,
  [sales] int
)
;
INSERT INTO yourtable ([state], [month], [ID], [sales])
  VALUES ('FL', 'June', 0001, '12000'),
  ('FL', 'June', 0001, '6000'),
  ('FL', 'June', 0001, '3000'),
  ('FL', 'July', 0001, '6000'),
  ('FL', 'July', 0001, '4000'),
  ('TX', 'January', 0050, '1000'),
  ('MI', 'April', 0032, '5000'),
  ('MI', 'April', 0032, '8000'),
  ('CA', 'April', 0032, '2000');

SELECT
  state,
  month,
  id,
  SUM(sales) Total
FROM yourtable
GROUP BY state,
         month,
         id;

-----Creating new table from old table

CREATE TABLE yourtable1 (
  [state] varchar(2),
  [month] varchar(7),
  [ID] int,
  [sales] int
)
;

----Inserting aggregation logic

INSERT INTO yourtable1 (state, month, id, sales)
  SELECT
    state,
    month,
    id,
    SUM(sales)
  FROM yourtable
  GROUP BY state,
           month,
           id;
-----Fetching records

SELECT
  *
FROM yourtable1;

赞(0）回复(0）举报 2021-06-25

twh00eeo2#

可以从select语句创建表。
所以你可以这样做：

create table sales_sum as 
  select 
     account,
     product,
     sum(qty),
     sum(amount) 
   from 
     sales
   group by 
     account, 
     product

这将创建一个具有正确结构的表，并插入您想要的记录。当然，您可以调整查询或表名。

赞(0）回复(0）举报 2021-06-25

sbtkgmzw3#

此查询执行etl工具可以执行的操作，但您需要运行整个脚本：

赞(0）回复(0）举报 2021-06-25

oxcyiej74#

假设新的销售在销售更新后被截断，然后开始重新充值，您可以使用insert..on duplicate key..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]>

赞(0）回复(0）举报 2021-06-25

我来回答

修改sql表以压缩相似的行，同时对列求和

4条答案

相关问题

热门标签

最新问答