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

来源：互联网发布：matlab 矩阵方程编辑：程序博客网时间：2024/05/21 05:56

本文剪接于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 |
+----+----------+------+

0 0