MySQL query filter duplicate data SQL statements

Query number of duplicate data

The code is as follows	Copy Code
Select device_id from Device group by device_id having count (device_id) > 1;

Query all duplicate data

The code is as follows	Copy Code
Select UserID, device_id, create_date from device where device_id to (select device_id from Device group by device_id Havi ng count (device_id) > 1) Order by device_id,create_date Desc;

Repeat one of the newest create_date in the section

The code is as follows	Copy Code
Select Max (create_date) from device group by DEVICE_ID has count (device_id) >1;

Filter queries

The code is as follows	Copy Code
SELECT * from device where device_id to (select device_id from Device group by DEVICE_ID have count (device_id) > 1) A nd create_date not in (select Max (create_date) from device group by DEVICE_ID have Count (device_id) >1) Order by Devic E_id,create_date desc;

Let's look at some examples below.

The table structure is as follows:

The code is as follows

Copy Code

mysql> desc test1; +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+----------------+ | ID | int (a) 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 and receiver need to do uniq key, but the design is not done, the following data have a lot of duplicate records.

1. Query the records that need to be deleted, and keep a record.

The code is as follows	Copy Code
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 has 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. Delete duplicate records, keep only one record. Note that subject,receiver to index, otherwise it will be slow.

The code is as follows	Copy Code
Delete A from test1 A, (select C.subject,c.receiver, Max (c.id) as bid from Test1 C where status=0 GROUP by Receiver,subje CT has count (1) >1) b where a.subject=b.subject and a.receiver = B.receiver and a.ID < b.bid;

All right, okay. SQL statements that filter for duplicate data that's probably it, if you can understand that there's little to worry about repeating the data.