mysql 查詢篩選重復數據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語句大概就這些了，如果你能理解那幾乎不擔心重復數據這一說了。