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 | 
+----+----------+------+