mysql如何刪除表中的重復(fù)行并保留id較?。ɑ蛘咻^大）的記錄?

數(shù)據(jù)庫如下:

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
  );

數(shù)據(jù)如下:

INSERT INTO `tdb_goods` VALUES ('14', 'AT7-7414LP 臺式電腦 （i5-3450四核 4G 500G 2G獨(dú)顯 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服務(wù)器', '3', '6', '5388.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('17', 'Mac Pro MD878CH/A 專業(yè)級臺式電腦', '3', '9', '28888.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('18', ' HMZ-T3W 頭戴顯示設(shè)備', '6', '7', '6999.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('19', '商務(wù)雙肩背包', '6', '7', '99.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('20', 'X3250 M4機(jī)架式服務(wù)器 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 頭戴顯示設(shè)備', '6', '7', '6999.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('23', '商務(wù)雙肩背包', '6', '7', '99.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('24', ' LaserJet Pro P1606dn 黑白激光打印機(jī)', '12', '4', '1849.000', '1', '0');

現(xiàn)在要刪除表中的重復(fù)行并保留id較小(或者較大的記錄),首先查詢出存在重復(fù)的記錄:

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;

結(jié)果如下:

可以看到此時(shí)取出了11條記錄,而其中存在重復(fù)的是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;

結(jié)果如下:

僅剩余兩條記錄.

問題:

**存在疑問的是t1.goods_id>t2.goods_id難道t2.goods_id為null的時(shí)候記錄是不與t1.goods_id進(jìn)行比較而直接排除嗎?
如果比較的話應(yīng)該是留有11條記錄的吧?結(jié)果為什么不是取得11條記錄而是只查詢出2條記錄?**