MYSQL用一条SQL语句删除重复记录-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_6250/article/details/81682001

本文介绍了一种使用MYSQL SQL语句删除表中重复记录的方法。通过一个具体的例子展示了如何利用子查询来找出并删除重复的数据行。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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