MySQL Список всех дубликатов

Возможный дубликат:
Найти дубликаты записей в MySQL

У меня есть таблица в MySQL:

ID    name    email
1    john     [email protected]
2    johnny   [email protected]
3    jim      [email protected]
4    Michael  [email protected]

Как я могу получить запрос MySQL, который будет перечислять дубликат, подобный этому?

Результат двойного поиска:

ID    name    email         Duplicate
1    john     [email protected]      2
2    johnny   [email protected]      2

Ответ 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 Demo

для повышения производительности, добавьте INDEX в столбце 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 Demo

Ответ 2

Если вы можете жить с идентификатором и именем в списках, разделенных запятыми, вы можете попробовать:

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

Это сохраняет объединение, хотя результат не в реляционном формате.

Ответ 3

Отметьте этот пост на форумах MySQL, который дает следующее:

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;