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不支持如下语句:
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 |
+----+----------+------+
下一篇: 布局学习——妙用TabHost