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
username varchar(10),
age
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
| 2 | aaa
| 3 | bbb
| 4 | bbb
| 5 | ccc
| 6 | ccc
| 7 | ddd
| 8 | ddd
+----+----------+------+
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
| 4 | bbb
| 6 | ccc
| 8 | ddd
+----+----------+------+