在MySQL中查找重复记录

本文翻译自：Find duplicate records in MySQL

I want to pull out duplicate records in a MySQL Database. 我想在MySQL数据库中提取重复记录。 This can be done with: 这可以通过以下方式完成：

1. SELECT address, count(id) as cnt FROM list
2. 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: 就像是：

1. JIM JONES 100 MAIN ST
2. 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. 我试图避免做第一个，然后在代码中用第二个查询查找重复项。

#1楼

参考：https://stackoom.com/question/3aCG/在MySQL中查找重复记录

#2楼

Another solution would be to use table aliases, like so: 另一种解决方案是使用表别名，如下所示：

1. SELECT p1.id, p2.id, p1.address
2. FROM list AS p1, list AS p2
3. WHERE p1.address = p2.address
4. AND p1.id != p2.id

All you’re really doing in this case is taking the original list table, creating two p retend tables — p 1 and p 2 — out of that, and then performing a join on the address column (line 3). 在这种情况下，您真正​​要做的就是获取原始列表表，从中创建两个p后端表-p 1和p 2 ，然后在address列上执行联接（第3行）。 The 4th line makes sure that the same record doesn’t show up multiple times in your set of results (“duplicate duplicates”). 第四行确保同一条记录不会在您的结果集中多次出现（“重复重复”）。

#3楼

select * from table_name t1 inner join (select distinct <attribute list> from table_name as temp)t2 where t1.attribute_name = t2.attribute_name

For your table it would be something like 对于您的桌子，它就像

select * from list l1 inner join (select distinct address from list as list2)l2 where l1.address=l2.address

This query will give you all the distinct address entries in your list table… I am not sure how this will work if you have any primary key values for name, etc.. 该查询将为您提供列表中所有不同的地址条目…如果您有名称等任何主键值，我不确定这将如何工作。

#4楼

Fastest duplicates removal queries procedure: 最快的重复项删除查询过程：

1. /* create temp table with one primary column id */
2. INSERT INTO temp(id) SELECT MIN(id) FROM list GROUP BY (isbn) HAVING COUNT(*)>1;
3. DELETE FROM list WHERE id IN (SELECT id FROM temp);
4. DELETE FROM temp;

#5楼

SELECT t.*,(select count(*) from city as tt where tt.name=t.name) as count FROM `city` as t where (select count(*) from city as tt where tt.name=t.name) > 1 order by count desc

Replace city with your Table. 用表格替换城市 。 Replace name with your field name 您的字段名称替换名称

#6楼

we can found the duplicates depends on more then one fields also.For those cases you can use below format. 我们发现重复项还取决于一个以上的字段。对于这些情况，您可以使用以下格式。

1. SELECT COUNT(*), column1, column2
2. FROM tablename
3. GROUP BY column1, column2
4. HAVING COUNT(*)>1