在 MySQL 中查找重复记录

2021-11-20 00:00:00 duplicates mysql

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

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

结果:

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;

相关文章