PHP Mysql 合并值(删除重复的电子邮件但保留值)

Question

我在 mysql 中有一个包含电子邮件的表。大多数电子邮件都是重复的，但每一封重复的电子邮件都在 col_1、col_2、col_3、col_4< 中包含不同的值。我想合并它们(删除重复项)并保留值。因为最终结果应该像第二张表。

+-----------------------------------------------------+-------++-------++-------++-------+
| email                                               | col_1 || col_2 || col_3 || col_4 |
+-----------------------------------------------------+-------++-------++-------++-------+
| a@a.com                                             |     1 ||     0 ||     0 ||     0 |
| b@b.com                                             |     0 ||     0 ||     1 ||     0 |
| a@a.com                                             |     0 ||     1 ||     0 ||     0 |
| d@d.com                                             |     1 ||     0 ||     0 ||     0 |
| a@a.com                                             |     0 ||     0 ||     0 ||     1 |
| b@b.com                                             |     0 ||     1 ||     0 ||     0 |
| d@d.com                                             |     0 ||     0 ||     0 ||     1 |
+-----------------------------------------------------+-------++-------++-------++-------+

我需要合并电子邮件列，但要保留 col_1、col_2、col_3、col_4 值

+-----------------------------------------------------+-------++-------++-------++-------+
| email                                               | col_1 || col_2 || col_3 || col_4 |
+-----------------------------------------------------+-------++-------++-------++-------+
| a@a.com                                             |     1 ||     1 ||     0 ||     1 |
| b@b.com                                             |     0 ||     1 ||     1 ||     0 |
| d@d.com                                             |     1 ||     0 ||     0 ||     1 |
+-----------------------------------------------------+-------++-------++-------++-------+

Answer 1

我认为最简洁的方法是创建另一个表并向其中插入您真正需要的值。然后，删除原来的表并重命名新表:

CREATE TABLE yourTableNew (email varchar(255), col_1 int, col_2 int, col_3 int, col_4 int)
INSERT INTO yourTableNew (email, col_1, col_2, col_3, col_4)
SELECT
    email,
    SUM(col_1),
    SUM(col_2),
    SUM(col_3),
    SUM(col_4)
FROM yourTable
GROUP BY email;

DROP TABLE IF EXISTS yourTable;
RENAME TABLE yourTableNew TO yourTable;