MySQL列出所有重复项

MySQL List All Duplicates(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 演示

这篇关于MySQL列出所有重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！