MySQL列出所有重复项

2022-01-10 00:00:00 sql duplicates select 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 演示

相关文章