mysql->sql一句sql删除重复数据_MySQL

bitsCN.com

mysql->sql一句sql删除重复数据

面试常考的一道题：一句sql删除表里的重复数据。

偶尔和同事聊到这个问题就顺便写了下代码，供大家参考~

//数据准备

Mysql代码

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);

删除语句：

Mysql代码

DELETE t

FROM

t_user t,

(

SELECT

min(id)AS ttid,

username

FROM

t_user t2

GROUP BY

t2.username

)AS tt

WHERE t.id > tt.ttid

and t.username = tt.username;

bitsCN.com