刪除特定影響因素(欄位列)下的重複記錄（MySQL）

標籤：

;CREATE TABLE TabTest(    `id` INT(11) NOT NULL AUTO_INCREMENT         ,`factorA` VARCHAR(255) NOT NULL DEFAULT ‘ ‘    ,`factorB` VARCHAR(255) NOT NULL DEFAULT ‘ ‘    ,`factorC` DECIMAL(10,2) NOT NULL DEFAULT 0    ,`remark` VARCHAR(255) NOT NULL DEFAULT ‘ ‘    , PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT=‘‘;INSERT INTO TabTest(factorA, factorB, factorC, remark)SELECT ‘A1‘, ‘B1‘, 0.5, ‘1..‘UNION ALL SELECT ‘A1‘, ‘B1‘, 0.5, ‘2..‘UNION ALL SELECT ‘A2‘, ‘B2‘, 0.5, ‘3..‘UNION ALL SELECT ‘A2‘, ‘B2‘, 1.5, ‘4..‘UNION ALL SELECT ‘A2‘, ‘B2‘, 0.5, ‘5..‘;SELECT * FROM TabTest;

-- 方案一;DELETE FROM TabTest WHERE id NOT IN ( SELECT * FROM ( SELECT id FROM TabTest GROUP BY factorA, factorB, factorC ) b );

-- 方案二DELETE FROM TabTest WHERE id IN (    -- MySQL Error 1093 – Can‘t specify target table for update in FROM clause    SELECT b.id FROM     (        SELECT tOuter.`id` FROM TabTest tOuter         INNER JOIN         (            SELECT tInner.id, tInner.factorA, tInner.factorB, tInner.factorC            FROM TabTest tInner            GROUP BY tInner.factorA, tInner.factorB, tInner.factorC            HAVING COUNT(1) > 1        ) a        ON tOuter.`factorA` = a.`factorA`        AND tOuter.`factorB` = a.`factorB`        AND tOuter.`factorC` = a.`factorC`        WHERE tOuter.`id` <> a.`id`    )b)

方案一: 資料量小時, 比較便捷

方案二: 資料量大時使用, 第一個方案在70萬資料下試過, 5分鐘出不來結果, 放棄, 使用第二種方案, 秒完.

刪除特定影響因素(欄位列)下的重複記錄（MySQL）