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

最新推荐文章于 2021-02-28 11:27:18 发布

原创最新推荐文章于 2021-02-28 11:27:18 发布 · 170 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL #SQL

Oracle 专栏收录该内容

36 篇文章

订阅专栏

本文介绍了一种使用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 |
+----+----------+------+