mysql查询筛选重复数据sql语句

以前讲过大量的重复数据过滤语句,下面小编来给大家介绍一些自己收藏了查询筛选重复数据sql语句,希望对各位朋友有所帮助.

查询重复数据数量,代码如下:

select device_id from device group by device_id having count(device_id) > 1;

查询所有重复数据,代码如下:

1. selectuserid,device_id,create_datefromdevicewheredevice_idin(selectdevice_idfromdevicegroupbydevice_idhavingcount(device_id)>1)orderbydevice_id,create_datedesc;

重复一条中create_date 最新的那一条,代码如下:

select max(create_date) from device group by device_id having count(device_id)>1;

筛选查询,代码如下:

1. select*fromdevicewheredevice_idin(selectdevice_idfromdevicegroupbydevice_idhavingcount(device_id)>1)andcreate_datenotin(selectmax(create_date)fromdevicegroupbydevice_idhavingcount(device_id)>1)orderbydevice_id,create_datedesc;

下面再看一些实例吧.

表结构如下,代码如下:

1. mysql>desctest1;
2. +--------------+------------------+------+-----+---------+----------------+
3. |Field|Type|Null|Key|Default|Extra|
4. +--------------+------------------+------+-----+---------+----------------+
5. |ID|int(10)unsigned|NO|PRI|NULL|auto_increment|
6. |SENDERNAME|varchar(32)|YES||NULL||
7. |RECEIVERNAME|varchar(64)|YES||NULL||
8. |SENDER|varchar(64)|NO||NULL||
9. |RECEIVER|varchar(64)|NO||NULL||
10. |SUBJECT|varchar(512)|NO||NULL||
11. |CONTENT|text|NO||NULL||
12. |PRIORITY|int(11)|NO|MUL|NULL||
13. |STATUS|int(11)|NO|MUL|NULL||
14. |CREATETIME|datetime|NO||NULL||
15. |SENDTIME|datetime|YES||NULL||
16. +--------------+------------------+------+-----+---------+----------------+

subject和RECEIVER,需要做uniq key,但设计时未做，,后面的数据就有很多重复的记录.

1.查询需要删除的记录,会保留一条记录,代码如下:

1. selecta.id,a.subject,a.RECEIVERfromtest1aleftjoin(selectc.subject,c.RECEIVER,max(c.id)asbidfromtest1cwherestatus=0GROUPBYRECEIVER,SUBJECThavingcount(1)>1)bona.id<b.bidwherea.subject=b.subjectanda.RECEIVER=b.RECEIVERanda.id<b.bid

2.删除重复记录,只保留一条记录,注意,subject,RECEIVER 要索引,否则会很慢的,代码如下:

1. deleteafromtest1a,(selectc.subject,c.RECEIVER,max(c.id)asbidfromtest1cwherestatus=0GROUPBYRECEIVER,SUBJECThavingcount(1)>1)bwherea.subject=b.subjectanda.RECEIVER=b.RECEIVERanda.id<b.bid;//phpfensi.com

好了筛选重复数据的sql语句大概就这些了,如果你能理解那几乎不担心重复数据这一说了.