如何在MySQL中查找重复项 [英] How to find duplicates in MySQL

问题描述

假设我有很多列.如果2列匹配并且完全相同，则它们是重复项.

Suppose I have many columns. If 2 columns match and are exactly the same, then they are duplicates.

ID | title | link | size | author

如果2行或更多行的链接和大小相似，则这些行是重复的. 如何将这些重复项放入列表并进行处理?

Suppose if link and size are similar for 2 rows or more, then those rows are duplicates. How do I get those duplicates into a list and process them?

推荐答案

将返回所有具有重复项的记录:

Will return all records that have dups:

SELECT theTable.*
FROM theTable
INNER JOIN (
  SELECT link, size
  FROM theTable 
  GROUP BY link, size
  HAVING count(ID) > 1
) dups ON theTable.link = dups.link AND theTable.size = dups.size

我喜欢子查询b/c，我可以做一些事情，例如选择除第一个或最后一个之外的所有内容. (然后很容易变成删除查询).

I like the subquery b/c I can do things like select all but the first or last. (very easy to turn into a delete query then).

示例:选择所有重复的记录，但最大ID为

Example: select all duplicate records EXCEPT the one with the max ID:

SELECT theTable.*
FROM theTable
INNER JOIN (
  SELECT link, size, max(ID) as maxID
  FROM theTable 
  GROUP BY link, size
  HAVING count(ID) > 1
) dups ON theTable.link = dups.link 
          AND theTable.size = dups.size 
          AND theTable.ID <> dups.maxID

这篇关于如何在MySQL中查找重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！