mysql删除重复数据记录sql语句总结

本文剪接于http://blog.163.com/shexinyang@126/blog/static/1367393122014112311331350/

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