问题描述
我使用
选择电子邮件,COUNT(电子邮件)AS发生
从酿酒厂
GROUP BY电子邮件
HAVING(COUNT(email)> 1);
根据他们的电子邮件查找重复。
但是现在我需要他们的ID才能定义哪一个正确地删除。
第二个约束是:我只想要最后的INSERTED重复。 / p>
所以如果有2个条目[email protected]作为一个电子邮件,他们的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 [email protected] 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!