MYSQL用一条SQL语句删除重复记录

MYSQL用一条SQL语句删除重复记录

MYSQL不支持如下语句：
delete from t_user where id in(select max(id) as id from t_user group by username );

MYSQL用以下词句就可以：
delete t_user from t_user , (select id from t_user group by username having count(*)>1 ) as t2 where t_user.id=t2.id;


例子如下:

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('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> select * from t_user;
+----+----------+------+
| id | username | age |
+----+----------+------+
| 1 | aaa          |    20 |
| 2 | aaa          |    20 |
| 3 | bbb          |    20 |
| 4 | bbb          |    20 |
| 5 | ccc          |    20 |
| 6 | ccc          |    20 |
| 7 | ddd          |    20 |
| 8 | ddd          |    20 |
+----+----------+------+

mysql> delete t_user from t_user , (select id from t_user group by username having count(*)>1 ) as t2 where t_user.id=t2.id;
Query OK, 4 rows affected (0.05 sec)


mysql> select * from t_user;
+----+----------+------+
| id | username | age |
+----+----------+------+
| 2 | aaa          |    20 |
| 4 | bbb          |    20 |
| 6 | ccc          |    20 |
| 8 | ddd          |    20 |
+----+----------+------+