mysql如何删除表中的重复行并保留id较小（或者较大）记录

在实际录入数据库的过程中，如果数据量比较大的话，难免会因为一些原因，而录入多条重复的记录，那么应该如何操作才能删除重复行，并且保留一条id较大，或者较小的记录呢。

在本例中所用数据表结构如下所示tdb_goods

![](https://img.huzhan.com/blog/20180922/20181537577525289.png)

表中数据重复如图所示

![](https://img.huzhan.com/blog/20180922/20181537577629571.png)

首先第一步，利用group by分组查出每组中数目大于2的（即重复记录的）内容
```sql
mysql> SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING COUN
T(goods_name)>=2;
```
```html
+----------+------------------------------+
| goods_id | goods_name                   |
+----------+------------------------------+
|       20 | X3250 M4机架式服务器 2583i14   |
|       19 | 商务双肩背包                   |
-------------------------------------------
``` 
然后使用LEFT JOIN使原始表和上述查询结果进行连接，删除重复记录，保留id较小的记录

```sql
mysql> DELETE t1 FROM tdb_goods AS t1 LEFT JOIN( SELECT goods_id,goods_name FROM
 tdb_goods GROUP BY goods_name HAVING COUNT(goods_name)>=2) AS t2 ON t1.goods_na
me = t2.goods_name WHERE t1.goods_id>t2.goods_id;
```
从上述语句可以看出，条件是名称相同的，然后删除所有goods_id大的记录。这样就可以实现想要的效果。
如果想保留id较大的同理，如下所示
```sql
mysql> DELETE t1 FROM tdb_goods AS t1 LEFT JOIN( SELECT max(goods_id) AS goods_i
d,goods_name FROM tdb_goods GROUP BY goods_name HAVING COUNT(goods_name)>=2) AS
t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id<t2.goods_id;
Query OK, 2 rows affected (0.03 sec)
```
下边记录一下我使用过的sql语句：

```sql
SELECT uid,id,username FROM qs_member GROUP BY username HAVING COUNT(username)>=2;  
```
```sql
DELETE t1 FROM qs_member AS t1 LEFT JOIN( SELECT uid,username FROM qs_member GROUP BY username HAVING COUNT(username)>=2) AS t2 ON t1.username = t2.username WHERE t1.uid>t2.uid;  
```

已有 0 条评论

Ta的最新文章

最近评论

标签大全

阅读目录