在MySQL中查找重复记录 [英] Find duplicate records in MySQL

问题描述

我想在MySQL数据库中提取重复的记录。这可以通过以下方式完成：

  SELECT address，count（id）as cnt FROM list 
 GROUP BY address HAVING cnt > 1 
  

其结果如下：

  100 MAIN ST 2 
  

我想拉它，显示每行是重复的。如下：

  JIM JONES 100 MAIN ST 
 JOHN SMITH 100 MAIN ST 
  

有什么想法可以做到这一点？我试图避免做第一个，然后在代码中查找第二个查询。

解决方案

键是重写此查询，以便它可以用作子查询。

  SELECT firstname，
 lastname，
 list.address 
 FROM list 
 INNER JOIN（SELECT address 
 FROM list 
 GROUP BY address 
 HAVING COUNT（id）> 1）dup 
 ON list.address = dup.address; 
  

I want to pull out duplicate records in a MySQL Database. This can be done with:

SELECT address, count(id) as cnt FROM list
GROUP BY address HAVING cnt > 1

Which results in:

100 MAIN ST    2

I would like to pull it so that it shows each row that is a duplicate. Something like:

JIM    JONES    100 MAIN ST
JOHN   SMITH    100 MAIN ST

Any thoughts on how this can be done? I'm trying to avoid doing the first one then looking up the duplicates with a second query in the code.

解决方案

The key is to rewrite this query so that it can be used as a subquery.

SELECT firstname, 
   lastname, 
   list.address 
FROM list
   INNER JOIN (SELECT address
               FROM   list
               GROUP  BY address
               HAVING COUNT(id) > 1) dup
           ON list.address = dup.address;

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