删除特定影响因素(字段列)下的重复记录（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)