Mysql开发技巧之删除重复数据

Mysql利用联表查询和分组来删除重复数据

//删除表中重复的id，保留最大的id
mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | a    |
|  6 | a    |
|  7 | c    |
|  8 | d    |
+----+------+
8 rows in set (0.08 sec)

mysql> delete a from user a left join (select max(id) as id, name from user group by name having(count(id)) > 1)  b on a
.name = b.name where a.id < b.id;
Query OK, 4 rows affected (0.13 sec)

//删除重复数据成功
mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  2 | b    |
|  6 | a    |
|  7 | c    |
|  8 | d    |
+----+------+