欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-03-07 13:55:06
...
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 |
+----+----------+------+
相关标签: MySQL SQL