在MySQL中查找重复记录

Question

我想从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

有没有关于如何做到这一点的想法？我试图避免在代码中进行第二个查询来查找重复项，而不是先执行第一个查询。

- Chris Bartow

Answer 1

关键是重写此查询，以便将其用作子查询。

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;

- Powerlord

---

小心使用子查询，子查询可能会对性能造成极大的影响。如果这种操作经常发生和/或涉及许多重复记录，建议将处理过程从数据库中移出，并移到数据集中进行。 - bdwakefield

这是一个无关联子查询，因此假设单独的查询不是设计不良，那么它就不会太糟糕。 - ʞɔıu

这是正确的想法，但如下所述，只有在地址被保证标准化的情况下才有效... - Matt

使用这个查询，你可以找到重复的数据，也可以找到三个、四个甚至更多重复的数据。 - albanx

如何修改以删除找到的重复行？ - BadHorsie

Answer 2

SELECT date FROM logs group by date having count(*) >= 2

- trt

---

这是在Laravel中最容易使用的工作查询。只需将->having(DB::raw('count(*)'), '>', 2) 添加到查询中即可。非常感谢！ - Kevin

请注意这个答案，它只会返回重复记录中的其中一个。如果您拥有多于两个相同的记录副本，则不会将它们全部显示出来，在删除返回的记录后，您的表中仍然存在重复项。 - Mikiko Jane

为什么要使用>=2？直接使用HAVING COUNT(*) > 1即可。 - BadHorsie

考虑到这并没有解决最初提出的问题（即如何返回所有重复项），所以我不同意。 - Michael

有人能解释一下为什么这个被赞得这么高吗？它看起来几乎和原问题中的第一段代码一模一样，而提问者说那段代码是不够好的。我错过了什么吗？ - GluePear

Answer 3

为什么不直接使用INNER JOIN将表与自身连接?

SELECT a.firstname, a.lastname, a.address
FROM list a
INNER JOIN list b ON a.address = b.address
WHERE a.id <> b.id

如果地址可能重复出现超过两次，则需要使用DISTINCT。

- rudolfson

---

我也进行了测试，在我的情况下（最新版本的MySQL，有12万行的表格），与被认可的解决方案相比，这种方法要慢将近6倍。这可能是因为它需要一个临时表，请对两种方法都运行EXPLAIN以查看差异。 - user215361

我将查询的最后一部分改为 WHERE a.id > b.id，仅过滤掉较新的重复项，这样我就可以直接在结果上执行 DELETE。将比较切换为列出较旧的重复项。 - Stoffe

这个程序运行了50秒，而@doublejosh的答案只用了0.13秒。 - antonagestam

我必须补充说明，尽管WHERE子句存在，但此答案会给出重复的答案，例如一个地址被三倍，输出行将增加一倍。如果是四倍，我相信响应将会是三倍。 - hyamanieu

我在leetcode "https://leetcode.com/problems/duplicate-emails/"中进行了测试。与子查询相比，它更快。 - billow

Answer 4

我尝试了为这个问题选择的最佳答案，但它有点让我困惑。实际上，我只需要在我的表格中的单个字段上进行操作。以下示例来自此链接对我非常有效：

SELECT COUNT(*) c,title FROM `data` GROUP BY title HAVING c > 1;

- Arman Malik

Answer 5

这难道不更简单吗：

SELECT *
FROM tc_tariff_groups
GROUP BY group_id
HAVING COUNT(group_id) >1

待翻译

- Tudor

---

对我来说很有效，因为我只需要处理大约10,000个重复行以使它们唯一，比加载全部600,000行要快得多。 - adrianTNT

非常容易 - Shwet

更简单，但解决的问题略有不同。被接受的答案显示每个重复行的所有行。而这个答案只显示每个重复行的一行，因为这就是 GROUP BY 的工作原理。 - ToolmakerSteve

Answer 6

select `cityname` from `codcities` group by `cityname` having count(*)>=2

这是与您所提出的类似的查询，它200%地有效且易于操作。祝使用愉快！

- pratswinz

Answer 7

使用此查询通过电子邮件地址查找重复用户...

SELECT users.name, users.uid, users.mail, from_unixtime(created)
FROM users
INNER JOIN (
  SELECT mail
  FROM users
  GROUP BY mail
  HAVING count(mail) > 1
) dupes ON users.mail = dupes.mail
ORDER BY users.mail;

- doublejosh

---

要找到实际的重复项，您只需要使用内部查询。这比其他答案快得多。 - antonagestam

Answer 8

我们可以根据多个字段来查找重复项。对于这些情况，您可以使用以下格式。

SELECT COUNT(*), column1, column2 
FROM tablename
GROUP BY column1, column2
HAVING COUNT(*)>1;

- KESAVAN PURUSOTHAMAN

Answer 9

找到重复地址比看起来要复杂得多，尤其是如果您要求准确性的话。在这种情况下，仅使用MySQL查询是不够的...

我在SmartyStreets工作，我们进行地址验证、去重等等其他工作，并且我已经看到了很多不同的具有类似问题的挑战。

有几个第三方服务可以为您标记列表中的重复项。仅使用MySQL子查询将无法考虑到地址格式和标准的差异。美国邮政服务（针对美国地址）拥有某些指南以使它们标准化，但只有少数供应商被认证可执行此类操作。

因此，我建议您最好的选择是将表格导出为CSV文件，例如，并将其提交给一个能力强大的列表处理器。其中之一是LiveAddress，它会在几秒钟到几分钟内自动为您完成任务。它会使用一个名为“Duplicate”的新字段和值为Y来标记重复行。

- Matt

---

+1，因为匹配地址字符串的困难度很高，尽管您可能需要指出OP的“重复记录”问题本身并不复杂，但在比较地址时会变得复杂。 - story

Answer 10

另一个解决方法是使用表别名，像这样：

SELECT p1.id, p2.id, p1.address
FROM list AS p1, list AS p2
WHERE p1.address = p2.address
AND p1.id != p2.id

在这种情况下，您实际上只是采取原始的list表，在其中创建两个p retend表——p1和p2，然后在地址列（第3行）上执行连接。第四行确保同一记录不会在结果集中显示多次（“重复的重复项”）。

- jerdiggity

---

运行良好。如果WHERE使用LIKE进行检查，则也会找到撇号。这会使查询变慢，但在我的情况下只需要一次。 - gossi