本文翻译自:Find duplicate records in MySQL
I want to pull out duplicate records in a MySQL Database. 我想在MySQL数据库中提取重复记录。 This can be done with: 这可以通过以下方式完成:
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: 就像是:
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. 我试图避免做第一个,然后在代码中用第二个查询查找重复项。
参考:https://stackoom.com/question/3aCG/在MySQL中查找重复记录
Another solution would be to use table aliases, like so: 另一种解决方案是使用表别名,如下所示:
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”). 第四行确保同一条记录不会在您的结果集中多次出现(“重复重复”)。
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.. 该查询将为您提供列表中所有不同的地址条目…如果您有名称等任何主键值,我不确定这将如何工作。
Fastest duplicates removal queries procedure: 最快的重复项删除查询过程:
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 您的字段名称替换名称
we can found the duplicates depends on more then one fields also.For those cases you can use below format. 我们发现重复项还取决于一个以上的字段。对于这些情况,您可以使用以下格式。