MySQL列出所有重复项

Question

我在MySQL中有一个这样的表：

ID    name    email
1    john     [e-mail]
2    johnny   [e-mail]
3    jim      [e-mail]
4    Michael  [e-mail]

我怎样才能有MySQL查询来列出像这样的重复项？

重复搜索的结果：

ID    name    email         Duplicate
1    john     [e-mail]      2
2    johnny   [e-mail]      2

Answer 1

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演示

为了获得更好的性能，请添加一个INDEXon列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演示