MySQL 列出所有重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14431772/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL List All Duplicates
提问by user1995781
Possible Duplicate:
Find duplicate records in MySQL
可能的重复：
在 MySQL 中查找重复记录
I have a table in MySQL like this:
我在 MySQL 中有一个这样的表：
ID name email
1 john [email protected]
2 johnny [email protected]
3 jim [email protected]
4 Michael [email protected]
How can I have the MySQL query that will list out the duplicate one like this?
我怎样才能让 MySQL 查询列出这样的重复项？
Result of duplicate search:
重复搜索的结果：
ID name email Duplicate
1 john [email protected] 2
2 johnny [email protected] 2
回答by John Woo
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
for better performance, add an INDEXon column EMail.
为了获得更好的性能，添加一个INDEXon column EMail。
OR
或者
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
回答by Gordon Linoff
If you can live with having the ID and name in comma separated lists, then you can try:
如果您可以接受以逗号分隔的列表中的 ID 和名称，那么您可以尝试：
select email, count(*) as numdups,
group_concat(id order by id), group_concat(name order by id)
from t
group by email
having count(*) > 1
This saves a join, although the result is not in a relational format.
尽管结果不是关系格式，但这会保存连接。
回答by hd1
Check this poston the MySQL forums, which gives the following:
SELECT t1.id, t1.name, t1.email FROM t1 INNER JOIN (
SELECT colA,colB,COUNT(*) FROM t1 GROUP BY colA,colB HAVING COUNT(*)>1) as t2
ON t1.email = t2.email;
