MYSQL去重复并用最少的数据删除重复行

Question

我正在开发一个MYSQL数据库，它有以下三列:电子邮件、姓名、姓氏。

我需要做的是删除电子邮件，因为我知道可以使用这样的函数(这个查询只是为了排序，而不是删除)：

select distinct emails, name, surname from emails;

或

select emails, name, surname from emails group by emails having count(*) >= 2;

然而，我也需要确保，当发现一个重复的电子邮件地址，保存的是一个有一个名称和/或姓氏的价值。

例如：

电子邮件

电话:1,000,000,000,000，

2-2- bob@bob.com -自愿性-2

在这种情况下，我想保留第一个结果，并删除第二个结果。

我一直在调查使用'case‘或'if’语句，但没有使用这些语句的经验。我试着用这些语句扩展上面的函数，但没有结果。

有人能指出我的正确方向吗？

PS:表中的第一列是一个自动递增的id值，以防有帮助。

更新1:到目前为止，下面的@Bohemian回答很有效，但在一个例子中失败了，在一个重复的电子邮件地址中，一行中有一个名字但没有姓氏，在下一行中它没有名字，但有一个姓。这两项记录都会保存下来。所有需要编辑的内容都是为了删除这两条记录中的一条，不管是哪一条。

更新2：@Bohemian的回答很棒，但是经过更多的测试后，我发现它有一个根本的缺陷，因为只有在重复的电子邮件行中，名字和姓氏字段才有数据(如上表中的第一个条目)。如果有一封电子邮件的副本，但没有一个行同时填写了姓名和姓氏字段，那么所有这些行都将被忽略，而不是重复。

此查询的最后一步将是解决如何删除不符合当前必要条件的重复项。如果一行只有名字，另一行只有姓氏，那么删除哪一行并不重要，因为电子邮件是最重要的东西。

Answer 1

您可以使用此DELETE查询，该查询是通用的，可以很容易地调整以支持更多字段：

DELETE tablename.*
FROM
  tablename LEFT JOIN (
    SELECT MIN(id) min_id
    FROM
      tablename t INNER JOIN (
        SELECT
          emails, MAX((name IS NOT NULL) + (surname IS NOT NULL)) max_non_nulls
        FROM
          tablename
        GROUP BY
          emails) m
      ON t.emails=m.emails
         AND ((t.name IS NOT NULL) + (t.surname IS NOT NULL))=m.max_non_nulls
    GROUP BY
      t.emails) ids
  ON tablename.id=ids.min_id
WHERE
  ids.min_id IS NULL

请看小提琴这里。

此查询返回每个电子邮件的最大非空字段数：

SELECT
  emails,
  MAX((name IS NOT NULL) + (surname IS NOT NULL)) max_non_nulls
FROM
  tablename
GROUP BY
  emails

然后，我将使用tablename加入这个查询，以获得每个具有最大非空字段数的电子邮件的最小ID：

SELECT MIN(id) min_id
FROM
  tablename t INNER JOIN (
    SELECT
      emails, MAX((name IS NOT NULL) + (surname IS NOT NULL)) max_non_nulls
    FROM
      tablename
    GROUP BY
      emails) m
  ON t.emails=m.emails
     AND ((t.name IS NOT NULL) + (t.surname IS NOT NULL))=m.max_non_nulls
GROUP BY
  t.emails

然后，我将删除所有具有此查询未返回的ID的行。

Answer 2

对于mysql的多表删除语法来说，这很容易

delete b
from mytable a
join mytable b
  on a.email = b.email
  and a.id != b.id
where a.name is not null
and a.surname is not null

Answer 3

删除带有重复电子邮件id的记录

delete 
    from duplicate_email where id in(
        select id from (
            select id, email from duplicate_email group by email having count(id) > 1) as id
    )

但是有一个问题，您可以删除那些只有一封重复电子邮件的记录，即两封相同的电子邮件，但如果有三封或更多的邮件，则可以重复此查询，直到删除零条记录为止。