MySQL – 查询重复记录
几种方法，记录一下。
1、查找表中多余的重复记录，重复记录是根据单个字段（mail_id）来判断
|
1 |
SELECT * FROM table WHERE mail_id IN (SELECT mail_id FROM table GROUP BY mail_id HAVING COUNT(mail_id) > 1); |
2、删除表中多余的重复记录，重复记录是根据单个字段（mail_id）来判断，只留有rowid最小的记录
|
1 |
DELETE FROM table WHERE mail_id IN (SELECT mail_id FROM table GROUP BY mail_id HAVING COUNT(mail_id) > 1) AND rowid NOT IN (SELECT MIN(rowid) FROM table GROUP BY mail_id HAVING COUNT(mail_id )>1); |
3、查找表中多余的重复记录（多个字段）
|
1 |
SELECT * FROM table WHERE (mail_id,phone) IN (SELECT mail_id,phone FROM table GROUP BY mail_id,phone HAVING COUNT(*) > 1); |
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
|
1 |
DELETE FROM table WHERE (mail_id,phone) IN (SELECT mail_id,phone FROM table GROUP BY mail_id,phone HAVING COUNT(*) > 1) AND rowid NOT IN (SELECT MIN(rowid) FROM table GROUP BY mail_id,phone HAVING COUNT(*)>1); |
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
|
1 |
SELECT * FROM table WHERE (a.mail_id,a.phone) IN (SELECT mail_id,phone FROM table GROUP BY mail_id,phone HAVING COUNT(*) > 1) AND rowid NOT IN (SELECT MIN(rowid) FROM table GROUP BY mail_id,phone HAVING COUNT(*)>1); |
抄自：某地
