MySQL列出所有重复项 [英] MySQL List All Duplicates
本文介绍了MySQL列出所有重复项的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
可能重复:
在 MySQL 中查找重复记录
我在 MySQL 中有一个这样的表:
I have a table in MySQL like this:
ID name email
1 john abc@abc.com
2 johnny abc@abc.com
3 jim eee@eee.com
4 Michael abec@awwbc.com
我怎样才能让 MySQL 查询像这样列出重复的查询?
How can I have the MySQL query that will list out the duplicate one like this?
重复搜索结果:
ID name email Duplicate
1 john abc@abc.com 2
2 johnny abc@abc.com 2
推荐答案
SELECT a.*, b.totalCount AS Duplicate
FROM tablename a
INNER JOIN
(
SELECT email, COUNT(*) totalCount
FROM tableName
GROUP BY email
) b ON a.email = b.email
WHERE b.totalCount >= 2
- SQLFiddle 演示
为了获得更好的性能，请在 EMail 列上添加 INDEX.
for better performance, add an INDEX on column EMail.
或
SELECT a.*, b.totalCount AS Duplicate
FROM tablename a
INNER JOIN
(
SELECT email, COUNT(*) totalCount
FROM tableName
GROUP BY email
HAVING COUNT(*) >= 2
) b ON a.email = b.email
- SQLFiddle 演示
这篇关于MySQL列出所有重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
