时间戳

首页 > 解决方案 > 是否有一个 mySQL 过程可以将重复的数据行合并为一个，然后允许我像处理一行一样操作该数据？

mysql - 是否有一个 mySQL 过程可以将重复的数据行合并为一个，然后允许我像处理一行一样操作该数据？

问题描述

我正在尝试提出一个存储过程，它需要多行完全相同，并将它们组合成一行，同时对一列求和，然后可以根据该一列的总和运行更多存储过程。

我尝试了一个GROUP BY语句，但实际上并没有将行组合在一起，因为如果我通过另一个过程运行表，它会执行操作，就好像每一行都没有组合一样。执行SELECT * FROM mytable查询显示每一行实际上并未合并为一个。

有没有办法将多行永久组合成一个单行？

首先，我有一张这样的表：

+-------+-----+--------+---------+------+-----+-----------+
| RowID | pID |  Name  |  Date   | Code | QTY | Purchased |
+-------+-----+--------+---------+------+-----+-----------+
|     1 |   1 | bob    | 9/29/20 |  123 |   1 |           |
|     2 |   1 | bob    | 8/10/20 |  456 |   1 |           |
|     3 |   2 | rob    | 9/15/20 |  123 |   1 |           |
|     4 |   2 | rob    | 9/15/20 |  123 |   1 |           |
|     5 |   2 | rob    | 9/15/20 |  123 |   1 |           |
|     6 |   2 | rob    | 9/15/20 |  123 |   1 |           |
|     7 |   2 | rob    | 9/15/20 |  123 |   1 |           |
|     8 |   3 | john   | 7/12/20 |  987 |   1 |           |
|     9 |   3 | john   | 7/12/20 |  987 |   1 |           |
|    10 |   4 | george | 9/12/20 |  684 |   1 |           |
|    11 |   5 | paul   | 2/2/20  |  454 |   1 |           |
|    12 |   6 | amy    | 1/12/20 |  252 |   1 |           |
|    13 |   7 | susan  | 5/30/20 |  131 |   1 |           |
|    14 |   7 | susan  | 6/6/20  |  252 |   1 |           |
|    15 |   7 | susan  | 5/30/20 |  131 |   1 |           |
+-------+-----+--------+---------+------+-----+-----------+

最后，我想要一张这样的桌子：

+-------+-----+--------+---------+------+-----+-----------+
| RowID | pID |  Name  |  Date   | Code | QTY | Purchased |
+-------+-----+--------+---------+------+-----+-----------+
|     1 |   1 | bob    | 9/29/20 |  123 |   1 |           |
|     2 |   1 | bob    | 8/10/20 |  456 |   1 |           |
|     3 |   2 | rob    | 9/15/20 |  123 |   5 |           |
|     4 |   3 | john   | 7/12/20 |  987 |   2 |           |
|     5 |   4 | george | 9/12/20 |  684 |   1 |           |
|     6 |   5 | paul   | 2/2/20  |  454 |   1 |           |
|     7 |   6 | amy    | 1/12/20 |  252 |   1 |           |
|     8 |   7 | susan  | 5/30/20 |  131 |   2 |           |
|     9 |   7 | susan  | 6/6/20  |  252 |   1 |           |
+-------+-----+--------+---------+------+-----+-----------+

将完全相同的行合并为一行，并对 QTY 字段求和，然后我可以将购买添加到总数中或从数量中扣除。使用 GROUP BY 语句可以实现这一点，但是当我去更改每个人的数量或添加购买时，它会将其视为第一个表，就好像实际上没有任何分组一样。

标签： mysqlsql

解决方案

So you have this table:

| RowID | pID |  Name  |  Date   | Code | QTY | Purchased |
+-------+-----+--------+---------+------+-----+-----------+
|     1 |   1 | bob    | 9/29/20 |  123 |   1 |           |
|     2 |   1 | bob    | 8/10/20 |  456 |   1 |           |
|     3 |   2 | rob    | 9/15/20 |  123 |   1 |           |
|     4 |   2 | rob    | 9/15/20 |  123 |   1 |           |
|     5 |   2 | rob    | 9/15/20 |  123 |   1 |           |
|     6 |   2 | rob    | 9/15/20 |  123 |   1 |           |
|     7 |   2 | rob    | 9/15/20 |  123 |   1 |           |
|     8 |   3 | john   | 7/12/20 |  987 |   1 |           |
|     9 |   3 | john   | 7/12/20 |  987 |   1 |           |
|    10 |   4 | george | 9/12/20 |  684 |   1 |           |
|    11 |   5 | paul   | 2/2/20  |  454 |   1 |           |
|    12 |   6 | amy    | 1/12/20 |  252 |   1 |           |
|    13 |   7 | susan  | 5/30/20 |  131 |   1 |           |
|    14 |   7 | susan  | 6/6/20  |  252 |   1 |           |
|    15 |   7 | susan  | 5/30/20 |  131 |   1 |           |

The best way, as has been suggested, is to create a new table with the content of your query, then to rename the old table, and the new table to the original table's name, to check if everything is all right, and to drop the original table if yes.

CREATE TABLE indata_new AS
WITH grp AS (
  SELECT
    MIN(rowid) AS orowid
  , pid
  , name
  , MAX(date) AS date
  , code
  , SUM(qty) AS qty
  FROM indata
  GROUP BY
    pid
  , name
  , code
)
SELECT
  ROW_NUMBER() OVER(ORDER BY orowid ASC) AS rowid
, *
FROM grp;

ALTER TABLE indata RENAME TO indata_old;
ALTER TABLE indata_new RENAME TO indata;
-- if "indata" now contains the data you want ...
SELECT * FROM indata;
-- out  rowid | orowid | pid |  name  |    date    | code | qty 
-- out -------+--------+-----+--------+------------+------+-----
-- out      1 |      1 |   1 | bob    | 2020-09-29 |  123 |   1
-- out      2 |      2 |   1 | bob    | 2020-08-10 |  456 |   1
-- out      3 |      3 |   2 | rob    | 2020-09-15 |  123 |   5
-- out      4 |      8 |   3 | john   | 2020-07-12 |  987 |   2
-- out      5 |     10 |   4 | george | 2020-09-12 |  684 |   1
-- out      6 |     11 |   5 | paul   | 2020-02-02 |  454 |   1
-- out      7 |     12 |   6 | amy    | 2020-01-12 |  252 |   1
-- out      8 |     13 |   7 | susan  | 2020-05-30 |  131 |   2
-- out      9 |     14 |   7 | susan  | 2020-06-06 |  252 |   1

-- you can ...
DROP TABLE indata_old;

推荐阅读