MySQL查询重复记录sql语句

在数据开发时我们常常会需要把数据库中重复的记录查出来或直接删除数据库中重复记录,下面我来给大家总结一些方法,有需要的朋友可参考.

常用的语句

1、查找表中多余的重复记录,重复记录是根据单个字段（mail_id）来判断,代码如下:

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最小的记录,代码如下:

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、查找表中多余的重复记录(多个字段),代码如下:

SELECT * FROM table WHERE (mail_id,phone) IN (SELECT mail_id,phone FROM table GROUP BY mail_id,phone HAVING COUNT(*) > 1);

4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录,代码如下:

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最小的记录,代码如下:

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);

存储过程,代码如下:

1. declare@maxinteger,@idinteger
2. declarecur_rowscursorlocalforselect主字段,count(*)from表名groupby主字段havingcount(*)>；1
3. opencur_rows
4. fetchcur_rowsinto@id,@max
5. while@@fetch_status=0
6. begin
7. select@max=@max-1
8. setrowcount@max
9. deletefrom表名where主字段=@id
10. fetchcur_rowsinto@id,@max
11. end--phpfensi.com
12. closecur_rows
13. setrowcount0