MySQL列出所有重复项

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

为了获得更好的性能，请添加一个

INDEX

EMail

或者

SELECt  a.*, b.totalCount AS DuplicateFROM    tablename a        INNER JOIN        ( SELECt  email, COUNT(*) totalCount FROM    tableName GROUP   BY email HAVINg  COUNT(*) >= 2        ) b ON a.email = b.email

• SQLFiddle演示