MySQL列出所有重复 [英] MySQL List All Duplicates

问题描述

可能重复：

在MySQL中查找重复记录

我有一个表MySQL这样：

  ID名称电子邮件
 1 john [e-mail] 
 2 johnny abc @ abc.com 
 3 jim [e-mail] 
 4 Michael [e-mail] 
  

/ p>

重复搜索的结果：

  ID名称电子邮件重复
 1 john [e-mail] 2 
 2 johnny [e-mail] 2 
  

解决方案

  SELECT a。*，b.totalCount AS重复
 FROM tablename a 
 INNER JOIN 
（
 SELECT email，COUNT（*）totalCount 
 FROM tableName 
 GROUP BY电子邮件
）b ON a.email = b.email 
 WHERE b.totalCount> = 2 
  

• SQLFiddle演示

为了更好的表现，添加一个 INDEX 列电子邮件。

或

  SELECT a。*，b.totalCount AS Duplicate 
 FROM tablename a 
 INNER JOIN 
（
 SELECT email，COUNT（*）totalCount 
 FROM tableName 
 GROUP BY电子邮件
 HAVING COUNT（*）> = 2 
）b ON a.email = b.email 
  

• SQLFiddle演示

Possible Duplicate:
Find duplicate records in MySQL

I have a table in MySQL like this:

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

How can I have the MySQL query that will list out the duplicate one like this?

Result of duplicate search:

ID    name    email         Duplicate
1    john     [e-mail]      2
2    johnny   [e-mail]      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 Demo

for better performance, add an INDEX on column EMail.

OR

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

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