使用MySQL查找最近重复的ID [英] Find most recent duplicates ID with MySQL

问题描述

我使用

 选择电子邮件，COUNT（电子邮件）AS发生
从酿酒厂
 GROUP BY电子邮件
 HAVING（COUNT（email）> 1）; 
  

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

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

第二个约束是：我只想要最后的INSERTED重复。 / p>

所以如果有2个条目[e-mail]作为一个电子邮件，他们的ID分别是40和12782，它只会删除12782条目并保留40条。 / p>

有什么想法可以做到这一点？我一直在捣碎SQL大约一个小时，似乎无法找到如何做到这一点。

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

解决方案

嗯，你回答你的问题。你似乎想要 max（id）：

  SELECT email，COUNT （电子邮件）AS发生，最大（id）
从酿酒厂
 GROUP BY电子邮件
 HAVING（COUNT（email）> 1）; 
  

您可以使用该语句删除其他用户。删除与 join 有一个棘手的语法，您必须先列出表名，然后从子句中指定加入：

 删除酿酒厂
从酿酒厂加入
（选择电子邮件，max（id）as maxid 
从酿酒厂
组通过电子邮件
有count（*）> 1 
）我们
在we.email = wineries.email和
 wineries.id < we.maxid; 
  

或将其写为存在子句：

 从酿酒厂删除
其中存在（选择1 
从（选择电子邮件，最大（id）为来自酿酒厂的$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ b b b b b b b b b b b b b b b b b b b b b b b b b  




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.

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

So if there's 2 entries with [e-mail] as an email and their IDs are respectively 40 and 12782 it would delete only the 12782 entry and keep the 40 one.

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.

Thanks and have a nice day!

解决方案

Well, you sort of answer your question. You seem to want 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:

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:

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
                 )

这篇关于使用MySQL查找最近重复的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！