mysql - 在MySQL中查找重复记录(Find duplicate records in MySQL)

Question

I want to pull out duplicate records in a MySQL Database.

(我想在MySQL数据库中提取重复记录。)

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.

(我试图避免做第一个，然后在代码中用第二个查询查找重复项。)

  ask by Chris Bartow translate from so

Answer 1

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;