MySQL查询重复记录sql语句
发布:smiling 来源: PHP粉丝网 添加日期:2014-10-08 22:16:12 浏览: 评论:0
在数据开发时我们常常会需要把数据库中重复的记录查出来或直接删除数据库中重复记录,下面我来给大家总结一些方法,有需要的朋友可参考.
常用的语句
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 select 主字段,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 --phpfensi.com
- close cur_rows
- set rowcount 0
Tags: MySQL查询重复 MySQL重复记录
相关文章
- ·mysql重复记录取最后一条记录方法(2014-10-03)
- ·mysql删除重复记录sql语句(2014-10-08)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)