SQL statement that removes duplicate records in MySQL and preserves one piece of data in duplicate data

Just want to write a delete duplicate statement and keep a data of SQL, online check a part of the information written in very detailed, but still here to write their own understanding, in order to follow the study. As follows:

Table fields and data:

SQL statements:

[SQL]View PlainCopy

1. DELETE from `User` WHEREID notinch(SELECT *  from(SELECTID from `User` GROUP byusername) asb)

Understand:

Let's start with the SQL inside.

1.SELECT ID from the ' user ' GROUP by username the ID of each group based on the name group.

2.SELECT * FROM (SELECT ID from ' user ' GROUP by username) as B There are 2 questions in this sentence,

First, why do you want to set such a select? MySQL does not support this approach because the query is used to update the data, and the queried data is updated again.

If this select query is not set, then the 1093-you can ' t specify target table ' user ' for update on FROM clause error will be reported.

Second, this sentence must take the alias, otherwise it will be reported 1248-every derived table must has its own alias error

3, combined with the above analysis to look at the entire SQL statement understanding, first to find out the group ID, and then delete the user table in the ID is not in the group ID of the data, then realize the effect.

Delete from table name where ID not in (column name of the Select ID from table name Group by group) alias

The effect is as follows:

Turn from:http://blog.csdn.net/dsiori/article/details/52806709

SQL statement that removes duplicate records in MySQL and preserves one piece of data in duplicate data