在MySQL中合并重复的行

Question

在MySQL中合并重复的行

4

我有一个像这样的数据库：

users
id    name    email                phone
1     bill    bill@fakeemail.com
2     bill    bill@fakeemail.com   123456789
3     susan   susan@fakeemail.com
4     john    john@fakeemail.com   123456789
5     john    john@fakeemail.com   987654321

我希望能够基于电子邮件字段合并重复记录。

正在尝试使用以下几点考虑：

基于电子邮件的重复进行合并
如果一行具有空值，则使用具有最多数据的行。
如果两行是重复的，但其他字段不同，则使用ID号最高的行（请参见 john@fakeemail.com 行的示例）。

这是我尝试过的查询语句：

DELETE FROM users WHERE users.id NOT IN 
(SELECT grouped.id FROM (SELECT DISTINCT ON (email) * FROM users) AS grouped)

出现了语法错误。

我正在尝试将数据库转换为这样，但是我无法找到正确的查询语句：

users
id   name    email                 phone
2    bill    bill@fakeemail.com    123456789
3    susan   susan@fakeemail.com   
5    john    john@fakeemail.com    987654321

- Jordash

2个回答

1

这是一个解决方案，将为结果表中每个用户的每个字段提供最新数据，因此满足您的第二个标准以及第一和第三个标准。它适用于您拥有的任意重复项，取决于GROUP_CONCAT上的group_concat_max_len条件。它使用GROUP_CONCAT准备每个用户字段所有值的列表，并按照最近的值排列。然后使用SUBSTRING_INDEX提取该列表中的第一个值，即最新值。此解决方案使用CREATE TABLE ... SELECT命令创建一个新的users表，然后删除旧表并将新表重命名为users。

CREATE TABLE users
    (`id` int, `name` varchar(5), `email` varchar(19), `phone` int)
;

INSERT INTO users
    (`id`, `name`, `email`, `phone`)
VALUES
    (1, 'bill', 'bill@fakeemail.com', 123456789),
    (2, 'bill', 'bill@fakeemail.com', NULL),
    (3, 'susan', 'susan@fakeemail.com', NULL),
    (4, 'john', 'john@fakeemail.com', 123456789),
    (5, 'john', 'john@fakeemail.com', 987654321)
;

CREATE TABLE newusers AS
SELECT id
     , SUBSTRING_INDEX(names, ',', 1) AS name
     , email
     , SUBSTRING_INDEX(phones, ',', 1) AS phone
FROM (SELECT id
           , GROUP_CONCAT(name ORDER BY id DESC) AS names
           , email
           , GROUP_CONCAT(phone ORDER BY id DESC) AS phones
      FROM users
      GROUP BY email) u;

DROP TABLE users;

RENAME TABLE newusers TO users;

SELECT * FROM users

输出：

id  name    email                   phone
1   bill    bill@fakeemail.com      123456789
4   john    john@fakeemail.com      987654321
3   susan   susan@fakeemail.com     (null)

在SQLFiddle上的演示

- Nick

1

如果表中存在与其他表的关联，删除用户表将失败。 - Christhofer Natalius

1

@ChristhoferNatalius同意，但删除行也是如此。 - Nick

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Tim Biegeleisen · Accepted Answer

这里有一种使用删除连接的选项：

DELETE
FROM users
WHERE id NOT IN (SELECT id
                 FROM (
                     SELECT CASE WHEN COUNT(*) = 1
                                 THEN MAX(id)
                                 ELSE MAX(CASE WHEN phone IS NOT NULL THEN id END) END AS id
                     FROM users
                     GROUP BY email) t);

这个删除操作的逻辑如下：

仅包含一条记录的电子邮件不会被删除
对于有两个或更多记录的电子邮件，我们会删除除具有最高id值且电话号码已定义的记录之外的所有记录。