MySQL使用多列为重复选择记录 [英] MySQL select records for duplicates using multiple columns

问题描述

我希望从表中选择记录，或将它们插入新的空白表中，其中多个列与数据库中的另一条记录相同.问题类似于此问题. 在MySQL中查找重复记录 但是，这仅比较一列.另外，我的一列(在下面的示例中为C列)是一个整数.就像上面链接中的问题一样，我希望返回每一行.不幸的是，我对联接如何如何独自解决这一问题还不够熟悉.我知道下面的代码与实际的SQL代码完全不一样，这只是我可以想到的最清晰的方式来描述我要进行的比较.

I would like the select records from a table, or insert them into a new blank table where multiple of the columns is the same as another record in the database. The problem is similar to this Question. Find duplicate records in MySQL However that only compares one column. Also, one of my columns, lets say column C in the example below, is an integer. Like the question in the link above, I want each of the rows to be returned. Unforunately I am just not familiar enough with how joins work to figure this out on my own yet. I know that the code below doesn't resemble the actual SQL code need at all, it is just the clearest way I can think to describe the comparisons I am trying to get.

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

任何帮助将不胜感激，我看到的所有从MYSQL选择重复项"问题都只使用一列作为比较.

Any help would be appreciated, all of the "select duplicates from MYSQL" questions I have seen use only one column as a comparison.

推荐答案

如果要计算多列中的重复项，请使用group by:

If you want to count duplicates among multiple columns, use group by:

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

如果只希望重复的值，则计数大于1.可使用having子句获得此值:

If you only want the values that are duplicated, then the count is bigger than 1. You get this using the having clause:

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

如果您实际上希望返回所有重复的行，则将最后一个查询返回到原始数据:

If you actually want all the duplicate rows returns, then join the last query back to the original data:

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

这将起作用，假设所有列值都不为NULL.如果是这样，请尝试:

This will work, assuming none of the column values are NULL. If so, then try:

     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运算符:

If you have NULL values, you can also use the NULL-safe operator:

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

这篇关于MySQL使用多列为重复选择记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！