MySQL MySQL使用多列选择重复记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16324328/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL select records for duplicates using multiple columns
提问by keepitreall89
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 MySQLHowever 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.
我想从表中选择记录，或者将它们插入到一个新的空白表中，其中多个列与数据库中的另一条记录相同。问题类似于这个问题。 在 MySQL 中查找重复记录但是，只比较一列。此外，我的一列，假设在下面的示例中列 C，是一个整数。就像上面链接中的问题一样，我希望返回每一行。不幸的是，我只是不太熟悉连接如何工作，无法自己解决这个问题。我知道下面的代码根本不像实际需要的 SQL 代码，它只是我能想到的最清晰的方式来描述我试图获得的比较。
SELECT ColumnE, ColumnA, ColumnB, ColumnC from table where (
Row1.ColumnA = Row2.ColumnA &&
Row1.ColumnB = Row2.ColumnB &&
Row1.ColumnC = Row2.ColumnC
)
Any help would be appreciated, all of the "select duplicates from MYSQL" questions I have seen use only one column as a comparison.
任何帮助将不胜感激，我见过的所有“从 MYSQL 中选择重复项”问题都只使用一列作为比较。
回答by Gordon Linoff
If you want to count duplicates among multiple columns, use group by:
如果要计算多列中的重复项，请使用group by：
select ColumnA, ColumnB, ColumnC, count(*) as NumDuplicates
from table
group by ColumnA, ColumnB, ColumnC
If you only want the values that are duplicated, then the count is bigger than 1. You get this using the havingclause:
如果您只想要重复的值，则计数大于 1。您可以使用以下having子句获得：
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
This will work, assuming none of the column values are NULL. If so, then try:
这将起作用，假设所有列值都不是 NULL。如果是这样，请尝试：
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)
EDIT:
编辑：
If you have NULLvalues, you can also use the NULL-safe operator:
如果您有NULL值，还可以使用NULL-safe 运算符：
on t.ColumnA <=> tsum.ColumnA and
t.ColumnB <=> tsum.ColumnB and
t.ColumnC <=> tsum.ColumnC
回答by mCube
why don't you try using union or creating temporary table. but personally, i do recommend using union than that of creating temporary table cause it would take you a longer time doing that. try doing this:
为什么不尝试使用联合或创建临时表。但就我个人而言，我确实建议使用联合而不是创建临时表，因为这样做会花费您更长的时间。尝试这样做：
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
)
hope this make sense.:)
希望这是有道理的。:)
