sql查询重复数据(查询筛选重复数据sql语句)
不多说 请仔细看下面
查询重复数据数量
select device_id from device group by device_id having count(device_id) > 1;
查询所有重复数据
select userid, device_id, create_date from device where device_id in (select device_id from device group by device_id having count(device_id) > 1) order by device_id,create_date desc ;
重复一条中create_date 最新的那一条
select max(create_date) from device group by device_id having count(device_id)>1;
筛选查询
select * from device where device_id in (select device_id from device group by device_id having count(device_id) > 1) and create_date not in (select max(create_date) from device group by device_id having count(device_id)>1) order by device_id,create_date desc ;
下面再看一些实例吧
表结构如下:
mysql> desc test1; +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+----------------+ | ID | int(10) unsigned | NO | PRI | NULL | auto_increment | | SENDERNAME | varchar(32) | YES | | NULL | | | RECEIVERNAME | varchar(64) | YES | | NULL | | | SENDER | varchar(64) | NO | | NULL | | | RECEIVER | varchar(64) | NO | | NULL | | | SUBJECT | varchar(512) | NO | | NULL | | | CONTENT | text | NO | | NULL | | | PRIORITY | int(11) | NO | MUL | NULL | | | STATUS | int(11) | NO | MUL | NULL | | | CREATETIME | datetime | NO | | NULL | | | SENDTIME | datetime | YES | | NULL | | +--------------+------------------+------+-----+---------+----------------+
subject和RECEIVER 需要做uniq key,但设计时未做,后面的数据就有很多重复的记录。
1. 查询需要删除的记录,会保留一条记录。
select a.id,a.subject,a.RECEIVER from test1 a left join (select c.subject,c.RECEIVER ,max(c.id) as bid from test1 c where status=0 GROUP BY RECEIVER, SUBJECT having count(1) >1) b on a.id< b.bid where a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id < b.bid
2. 删除重复记录,只保留一条记录。注意,subject,RECEIVER 要索引,否则会很慢的。
delete a from test1 a, (select c.subject,c.RECEIVER ,max(c.id) as bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b where a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id < b.bid;
好了筛选重复数据的sql语句大概就这些了,如果你能理解那几乎不担心重复数据这一说了。
除注明外的文章,均为来源:老汤博客,转载请保留本文地址!
原文地址:https://tangjiusheng.com/it/748.html
原文地址:https://tangjiusheng.com/it/748.html
大家都在看
- php开发人员必需掌握的MySQL数据类型
- mysql常见的聚合函数有哪些(数据库聚合函数简介)
- SQL修改语句怎么写(SQL语句添加和修改字段)
- 数据库sql server和mysql的区别大吗(sql server和mysql哪个好)
- mongodb和mysql的区别(mongodb和mysql哪个好)
- mysql删除数据库命令(教你mysql删除数据库方法)
- mysql内连接和外连接的区别(内连接和外连接详解)
- MySQL utf8 中文乱码(彻底解决 MySQL中的乱码问题办法)
- SQL修改语句怎么写(用SQL语句添加和修改字段)
- 数据库增删改查sql基本语句(常用的四种sql增删改查命令介绍)