查找和删除两列重复的行 [英] Find and remove duplicate rows by two columns
问题描述
我阅读了所有相关的重复问题/答案,发现这是最相关的答案:
I read all the relevant duplicated questions/answers and I found this to be the most relevant answer:
INSERT IGNORE INTO temp(MAILING_ID,REPORT_ID)
SELECT DISTINCT MAILING_ID,REPORT_IDFROM table_1
;
问题是我想删除col1和col2的重复项,但还想将table_1的所有其他字段包括在插入中.
The problem is that I want to remove duplicates by col1 and col2, but also want to include to the insert all the other fields of table_1.
我试图通过这种方式添加所有相关列:
I tried to add all the relevant columns this way:
INSERT IGNORE INTO temp(M_ID,MAILING_ID,REPORT_ID,
MAILING_NAME,VISIBILITY,EXPORTED) SELECT DISTINCT
M_ID,MAILING_ID,REPORT_ID,MAILING_NAME,VISIBILITY,
EXPORTED FROM table_1
;
M_ID(int,primary),MAILING_ID(int),REPORT_ID(int),
MAILING_NAME(varchar),VISIBILITY(varchar),EXPORTED(int)
但是它将所有行插入到临时文件中(包括重复项)
But it inserted all rows into temp (including duplicates)
推荐答案
由多列删除重复行的最佳方法是最简单的方法:
The best way to delete duplicate rows by multiple columns is the simplest one:
添加唯一索引:
ALTER IGNORE TABLE your_table ADD UNIQUE (field1,field2,field3);
上面的IGNORE可确保仅保留找到的第一行,其余的将被丢弃.
The IGNORE above makes sure that only the first found row is kept, the rest discarded.
(如果您以后需要重复和/或知道它们不再发生,则可以删除该索引).
(You can then drop that index if you need future duplicates and/or know they won't happen again).
这篇关于查找和删除两列重复的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!