mysql删除重复数据语句_mysql删除重复数据记录sql语句总结

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 |

+----+----------+------+