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); |
存储过程
代码如下 | 复制代码 |
declare @max integer,@id integer declare cur_rows cursor local for 主字段,count(*) from 表名 group by 主字段 having count(*) >; 1 open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin select @max = @max -1 set rowcount @max delete from 表名 where 主字段 = @id fetch cur_rows into @id,@max end close cur_rows set rowcount 0 |
推荐阅读
-
linux下开启mysql慢查询,分析查询语句_MySQL
-
SQL设置SQLServer最大连接数查询语句
-
多个查询条件的sql语句的拼写技巧,求指点。
-
sql server跟my sql 命令(语句)的区别,sql server与mysql的比
-
MySql的sql语句涉及group/sum/limit/结果集多字段,如何写
-
MySQL 最高速清空数据表数据的 SQL 语句
-
PHP+Mysql实现多关键字与多字段生成SQL语句的函数
-
MySQL之——查询重复记录、删除重复记录方法大全
-
使用distinct在mysql中查询多条不重复记录值的解决办法
-
Shell脚本中执行sql语句操作mysql的5种方法