使用MySQL查找最新的重复ID

I use to do

我用来做

SELECT email, COUNT(email) AS occurences
FROM wineries
GROUP BY email
HAVING (COUNT(email) > 1);

to find duplicates based on their email.

根据他们的电子邮件查找重复项。

But now I'd need their ID to be able to define which one to remove exactly.

但现在我需要他们的ID才能定义哪一个要删除。

The second constraint is: I want only the LAST INSERTED duplicates.

第二个约束是：我只想要LAST INSERTED重复项。

So if there's 2 entries with test@test.com as an email and their IDs are respectively 40 and 12782 it would delete only the 12782 entry and keep the 40 one.

因此，如果有2个条目将test@test.com作为电子邮件，并且它们的ID分别为40和12782，则它将仅删除12782条目并保留40条。

Any ideas on how I could do this? I've been mashing SQL for about a hour and can't seem to find exactly how to do this.

有关如何做到这一点的任何想法？我一直在捣碎SQL大约一个小时，似乎无法确切地找到如何做到这一点。

Thanks and have a nice day!

感谢，并有一个愉快的一天！

4 个解决方案

#1

Well, you sort of answer your question. You seem to want max(id):

好吧，你有点回答你的问题。你似乎想要max（id）：

SELECT email, COUNT(email) AS occurences, max(id)
FROM wineries
GROUP BY email
HAVING (COUNT(email) > 1);

You can delete the others using the statement. Delete with join has a tricky syntax where you have to list the table name first and then specify the from clause with the join:

您可以使用该语句删除其他人。使用连接删除有一个棘手的语法，您必须先列出表名，然后使用连接指定from子句：

delete wineries
            from wineries join
            (select email, max(id) as maxid
             from wineries
             group by email
             having count(*) > 1
            ) we
            on we.email = wineries.email and
               wineries.id < we.maxid;

Or writing this as an exists clause:

或者将其写为exists子句：

delete from wineries
    where exists (select 1
                  from (select email, max(id) as maxid
                        from wineries
                        group by email
                       ) we
                  where we.email = wineries.email and wineries.id < we.maxid
                 )

#2

select email, max(id), COUNT(email) AS occurences
FROM wineries
GROUP BY email
HAVING (COUNT(email) > 1);

#3

delete from wineries
where id not in
(
  select * from 
  ( 
     select min(id)
     from wineries
     group by email
  ) x
)

You need a subquery to trick MySQL to delete from a table it is selecting from at the same time.

您需要一个子查询来欺骗MySQL从它同时选择的表中删除。

#4

DELETE duplicates.*
FROM wineries
JOIN wineries AS duplicates USING (email)
WHERE duplicates.id < wineries.id;

play with it on sqlfiddle.com

在sqlfiddle.com上玩它

#1