我希望从一个表中 Select 记录，或者将它们插入一个新的空白表，其中多个列与数据库中的另一个记录相同.问题与这个问题类似.
SELECT ColumnE, ColumnA, ColumnB, ColumnC from table where (
Row1.ColumnA = Row2.ColumnA &&
Row1.ColumnB = Row2.ColumnB &&
Row1.ColumnC = Row2.ColumnC
)
任何帮助都将不胜感激，我看到的所有"从MYSQL中 Select 重复项"问题都只使用一列作为比较.
如果要计算多个列中的重复项，请使用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安全运算符:
on t.ColumnA <=> tsum.ColumnA and
t.ColumnB <=> tsum.ColumnB and
t.ColumnC <=> tsum.ColumnC