mysql 多列比较_MySQL使用多列为重复选择记录

小编典典

如果要计算多个列之间的重复项，请使用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

假设所有列值都不为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)

编辑：

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

on t.ColumnA <=> tsum.ColumnA and

t.ColumnB <=> tsum.ColumnB and

t.ColumnC <=> tsum.ColumnC

2020-05-17