Mysql-& gt; SQL statement: SQL statement used to delete duplicate data _ MySQL

Mysql-amp; gt; SQL statement to delete duplicate data bitsCN.com

Mysql-> SQL statement: delete duplicate data

A question frequently asked during the interview: an SQL statement is used to delete duplicate data in the table.

Occasionally, when I talked to my colleagues about this issue, I wrote the code for your reference ~

// Data preparation

Mysql code

Drop table t_user;

Create table t_user (

Id int (5) not null auto_increment,

Username varchar (10 ),

Age int (3 ),

Primary key (id)

);

Insert into t_user (username, age) values ('AAA', 20 );

Insert into t_user (username, age) values ('AAA', 20 );

Insert into t_user (username, age) values ('AAA', 20 );

Insert into t_user (username, age) values ('BBB ', 20 );

Insert into t_user (username, age) values ('BBB ', 20 );

Insert into t_user (username, age) values ('CCC ', 20 );

Insert into t_user (username, age) values ('CCC ', 20 );

Insert into t_user (username, age) values ('ddd ', 20 );

Insert into t_user (username, age) values ('ddd ', 20 );

Delete statement:

Mysql code

DELETE t

FROM

T_user t,

(

SELECT

Min (id) AS ttid,

Username

FROM

T_user t2

GROUP

T2.username

) AS tt

WHERE t. id> tt. ttid

And t. username = tt. username;

BitsCN.com