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

数据库如下:

tdb_goods表:

CREATE TABLE IF NOT EXISTS tdb_goods(

goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

goods_name VARCHAR(150) NOT NULL,

goods_cate VARCHAR(40) NOT NULL,

brand_name VARCHAR(40) NOT NULL,

goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,

is_show BOOLEAN NOT NULL DEFAULT 1,

is_saleoff BOOLEAN NOT NULL DEFAULT 0

);

数据如下:

INSERT INTO `tdb_goods` VALUES ('14', 'AT7-7414LP 台式电脑 (i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux )', '1', '4', '3699.000', '1', '0');

INSERT INTO `tdb_goods` VALUES ('15', 'Z220SFF F4F06PA工作站', '3', '5', '4288.000', '1', '0');

INSERT INTO `tdb_goods` VALUES ('16', 'PowerEdge T110 II服务器', '3', '6', '5388.000', '1', '0');

INSERT INTO `tdb_goods` VALUES ('17', 'Mac Pro MD878CH/A 专业级台式电脑', '3', '9', '28888.000', '1', '0');

INSERT INTO `tdb_goods` VALUES ('18', ' HMZ-T3W 头戴显示设备', '6', '7', '6999.000', '1', '0');

INSERT INTO `tdb_goods` VALUES ('19', '商务双肩背包', '6', '7', '99.000', '1', '0');

INSERT INTO `tdb_goods` VALUES ('20', 'X3250 M4机架式服务器 2583i14', '3', '1', '6888.000', '1', '0');

INSERT INTO `tdb_goods` VALUES ('21', '玄龙精英版 笔记本散热器', '6', '2', '0.000', '1', '0');

INSERT INTO `tdb_goods` VALUES ('22', ' HMZ-T3W 头戴显示设备', '6', '7', '6999.000', '1', '0');

INSERT INTO `tdb_goods` VALUES ('23', '商务双肩背包', '6', '7', '99.000', '1', '0');

INSERT INTO `tdb_goods` VALUES ('24', ' LaserJet Pro P1606dn 黑白激光打印机', '12', '4', '1849.000', '1', '0');

现在要删除表中的重复行并保留id较小(或者较大的记录),首先查询出存在重复的记录:

SELECT t1.goods_id, t1.goods_name, t2.goods_id AS goods_id1, t2.goods_name AS goods_name1 FROM `tdb_goods` AS t1 LEFT JOIN ( SELECT goods_id, goods_name FROM `tdb_goods` GROUP BY goods_name HAVING COUNT(goods_id) >= 2 ) AS t2 ON t1.goods_name = t2.goods_name;

结果如下:

可以看到此时取出了11条记录,而其中存在重复的是id为18,19的记录,其他均为null

加上比较条件 t1.goods_id > t2.goods_id,代码如下:

SELECT t1.goods_id, t1.goods_name, t2.goods_id AS goods_id1, t2.goods_name AS goods_name1 FROM `tdb_goods` AS t1 LEFT JOIN ( SELECT goods_id, goods_name FROM `tdb_goods` GROUP BY goods_name HAVING COUNT(goods_id) >= 2 ) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;

结果如下:

仅剩余两条记录.

问题:

**存在疑问的是t1.goods_id>t2.goods_id难道t2.goods_id为null的时候记录是不与t1.goods_id进行比较而直接排除吗?

如果比较的话应该是留有11条记录的吧?结果为什么不是取得11条记录而是只查询出2条记录?**