使用多个列选择MySQL中的重复记录

Question

我想从一个表中选择记录，或将它们插入到一个新的空表中，其中多个列与数据库中的另一条记录相同。这个问题类似于这个问题： 在MySQL中查找重复记录 然而，它只比较了一列。另外，我的某个列（例如下面示例中的列C）是整数。就像上面链接中的问题一样，我想要每行都返回。不幸的是，我对连接操作还不够熟悉，无法自己解决这个问题。我知道下面的代码根本不像实际需要的SQL代码，但这是我能想到的描述我想要得到的比较清晰的方式。

SELECT ColumnE, ColumnA, ColumnB, ColumnC from table where (
  Row1.ColumnA = Row2.ColumnA &&
  Row1.ColumnB = Row2.ColumnB &&
  Row1.ColumnC = Row2.ColumnC
)

非常感谢您的帮助，我看到的所有“从MYSQL选择重复项”的问题都只使用一个列作为比较。

- keepitreall89

Answer 1

如果您想在多列中计算重复项，请使用 group by：

select ColumnA, ColumnB, ColumnC, count(*) as NumDuplicates
from table
group by ColumnA, ColumnB, ColumnC

如果您只想获取重复的值，则计数大于1。您可以使用having子句来获取：

select ColumnA, ColumnB, ColumnC, count(*) as NumDuplicates
from table
group by ColumnA, ColumnB, ColumnC
having NumDuplicates > 1

如果你确实想要返回所有重复的行，则将最后一个查询与原始数据连接起来：

select t.*
from table t join
     (select ColumnA, ColumnB, ColumnC, count(*) as NumDuplicates
      from table
      group by ColumnA, ColumnB, ColumnC
      having NumDuplicates > 1
     ) tsum
     on t.ColumnA = tsum.ColumnA and t.ColumnB = tsum.ColumnB and t.ColumnC = tsum.ColumnC

假设列值均不为空，此方法将有效。如果有空值，则尝试使用以下方法：

     on (t.ColumnA = tsum.ColumnA or t.ColumnA is null and tsum.ColumnA is null) and
        (t.ColumnB = tsum.ColumnB or t.ColumnB is null and tsum.ColumnB is null) and
        (t.ColumnC = tsum.ColumnC or t.ColumnC is null and tsum.ColumnC is null)

编辑：

如果您有NULL值，您也可以使用NULL-safe运算符：

     on t.ColumnA <=> tsum.ColumnA and
        t.ColumnB <=> tsum.ColumnB and
        t.ColumnC <=> tsum.ColumnC 

- Gordon Linoff

---

像往常一样，你的回答很出色。但是，最后一个示例可以使用MySQL的"null-safe"等于运算符进行重写：on t.ColumnA <=> tsum.ColumnA and t.ColumnB <=> tsum.ColumnB and t.ColumnC <=> tsum.ColumnC。 - Ross Smith II

这个答案似乎可行，但返回的数量与我的预期有很大不同。可能需要编写一些使用for循环来进行双重检查并让我放心的Java代码。非常感谢您以这种方式格式化SQL代码，使用新行和缩进，这确实帮助我理解您的代码实际在做什么！ - keepitreall89

后验概率已保存。谢谢你。 - Parapluie

Answer 2

为什么不尝试使用union或创建临时表呢？但是个人建议使用union而不是创建临时表，因为那样会花费更长的时间。可以尝试这样做：

  select field1, field2 from(
   select '' as field2, field1, count(field1) as cnt FROM list GROUP BY field2 HAVING cnt > 1
    union
    select ''as field1, field2, cound(field2) as cnt from list group by field1 having cnt > 1
  )

希望这有意义。 :)

- mCube