mysql删除表中的重复行，保留ID较小（或较大）的记录

在实际的数据库输入过程中，如果数据量比较大，难免会出现一些记录存在多条重复记录的情况。那么，我们应该如何去除重复行并保留ID更大或更少的记录呢？

本例使用的数据表结构是下面的tdb_goods

表中数据重复如图

首先使用分组找出数量。每组大于2个（即重复存储）内容

1. mysql> SELECT Goods_id, Goods_name FROM tdb_goods GROUP BY Goods_name HAVING COUN
2. T(goods_name)>=2;----- --- + ---- ---------------------------+
3. |商标 |产品名称 |
4. +---------------------+ --------------------------------- - -+
5. | 20 | X3250 M4 机架服务器 2583i14 |
6. | 19 |商务背包|
7. -------------------------------------------------------- ------ -

然后使用LEFT JOIN命令将原表与上述查询结果连接起来，去掉重复记录，保留ID较小的记录

1. mysql> DELETE t1 FROM tdb_goods AS t1 LEFT JOIN( SELECT item_id, item_name FROM
2. tdb_goods GROUPS BY Goods_name HAVING COUNT(goods_name)>=2) AS t2 ON t1.goods_na
3. me = t2.goods_name WHERE.goods_id2od_id>sgood_sgood.从上面的表达式可以看出，条件是名称相同，然后删除所有item ID为大写的记录。这样就可以达到想要的效果了。
   如果你想保持相同的更大的ID，如下所示
   1. mysql> DELETE t1 FROM tdb_goods AS t1 LEFT JOIN( SELECT max(goods_id) AS products_i
   goods_name bromods_name, products COUNT (goods_name)> =2) AS
4. t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id=2;

1. DELETE t1 FROM qs_member AS t1 LEFT GROUP JOIN( SELECT AVING_member_name,qYus COUNT(username)>=2) AS t2 ON t1。用户名 = t2.用户名 WHERE t1.uid>t2.uid;