One SQL statement is used to remove one MySQL retained.

One SQL statement is used to remove one MySQL retained.

A few days ago, when I made a requirement, I needed to clear repeated records in mysql. The idea was to write them through code traversal, and then I thought it was too complicated, I thought that I should be able to solve the problem through an SQL statement. After checking the information, I have obtained a very convenient SQL statement. Here I will share with you the SQL statements and ideas.

Requirement Analysis

Duplicate records exist in the database, and one record is deleted and retained (whether to repeat the benchmark for multiple fields)

Solution

When you encounter this requirement, you may have some ideas in mind. The fastest way to think of it is to use an SQL statement to solve the problem. However, you can't help yourself with complicated SQL statements.

Ask for help

This demand was a little anxious, so I first thought that I could solve the problem with my colleagues in this field, and then share the problem with my colleagues. As a result, I just gave it to Baidu, I just gave me an SQL statement that I never used before, so that I could try it myself...

Baidu

An SQL statement is found:

DELETEFROM vitae aWHERE (a.peopleId, a.seq) IN (  SELECT   peopleId,   seq  FROM   vitae  GROUP BY   peopleId,   seq  HAVING   count(*) > 1 )AND rowid NOT IN ( SELECT  min(rowid) FROM  vitae GROUP BY  peopleId,  seq HAVING  count(*) > 1)

This statement is found in the article [only one record is retained for deleting duplicate data in MySQL. This SQL statement has the following three steps:

SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING count(*) > 1Query duplicate records in a table as a condition

SELECT min(rowid) FROM vitae GROUP BY peopleId, seq HAVING count(*) > 1The minimum ID value in the repeated records in the query results is the second condition.

Delete according to the preceding two conditions.DivisionOther duplicate records with the minimum ID

However, if an error occurs when running this statement, the following error occurs: you cannot update the table at the same time during the query.

Code Solution

Based on the preceding SQL statement, you can use the code in two steps to achieve the same purpose:

Retrieve repeated datasets first

Deletes the remaining duplicate data cyclically Based on the queried dataset.

The idea is there, and writing is also very fast, but it is a surprise to me, actually need116 sThen, you need to find the available SQL statements and paste the code and running results:

Perfect [deduplication] SQL

Finally, I got the perfect answer in a technical group. Let's look at this SQL statement:

DELETE consum_recordFROM consum_record,  (  SELECT   min(id) id,   user_id,   monetary,   consume_time  FROM   consum_record  GROUP BY   user_id,   monetary,   consume_time  HAVING   count(*) > 1 ) t2WHERE consum_record.user_id = t2.user_id  and consum_record.monetary = t2.monetary and consum_record.consume_time = t2.consume_timeAND consum_record.id > t2.id;

The preceding SQL statement can be understood in three steps:

(SELECT min(id) id, user_id, monetary, consume_time FROM consum_record GROUP BY user_id, monetary, consume_time HAVING count(*) > 1 ) t2Duplicate records are queried to form a set (temporary table t2). The set contains the minimum ID of each duplicate record.

consum_record.user_id = t2.user_id and consum_record.monetary = t2.monetary and consum_record.consume_time = t2.consume_time AssociationJudge fields of repeated benchmarks

Delete records with IDs greater than the IDs in t2 according to the conditions.

When I saw this statement, I thought it was too powerful. Such a simple SQL statement can solve such a complicated problem ~

It is also super fast to run. The original code is executed cyclically and requires116 sLeft and right, and here0.3 sThat's it. It's amazing ~

Summary

As a php program, SQL cannot be slowed down here. In practice, there are too many things to be busy, and the current SQL level is only at a common level, in the future, you must make up your knowledge in this area.

I will share it here today.