MYSQL 删除重复记录,只保留最大ID

table : productprice

table column : id ,productAsin ...

同一个productAsin 只保留最近的那条,即只保留ID 最大的.

第一步: 查出有重复的记录和最大的id

SELECT productAsin,MAX(id) maxId 
FROM productprice GROUP BY productAsin HAVING COUNT(id) >= 2)
tempt

第二步:查出在重复记录的,并且小于最大的id

SELECT productprice.id AS id FROM 
　　productprice productprice,(SELECT productAsin,MAX(id) maxId 
　　FROM productprice  GROUP BY productAsin HAVING COUNT(id) >= 2)
　　tempt
WHERE productprice.productAsin = tempt.productAsin 
AND productprice.id < tempt.maxId

第三步：删除

DELETE FROM productprice WHERE id IN (
SELECT tempout.id FROM 
(
SELECT productprice.id AS id FROM 
productprice productprice,(SELECT productAsin,MAX(id) maxId 
FROM productprice  GROUP BY productAsin HAVING COUNT(id) >= 2)
tempt
WHERE productprice.productAsin = tempt.productAsin
AND productprice.id < tempt.maxId) tempout) 
;

临时表 tempout 是为了防止MYSQL ERROR ：

 // MYSQL ERROR : Error Code: 1093. You can't specify target table 'productprice' for update in FROM clause

 

 

example : productprice 表 同一天,同一个productAsin 只保留ID最大的那条

DELETE FROM productprice WHERE id IN (
SELECT tempout.id FROM 
(
SELECT productprice.id AS id FROM 
productprice productprice,(SELECT productAsin,DATE_FORMAT(createdDate,'%Y-%m-%d') AS tempDate,MAX(id) maxId 
FROM productprice  GROUP BY DATE_FORMAT(createdDate,'%Y-%m-%d'),productAsin HAVING COUNT(id) >= 2)
tempt
WHERE productprice.productAsin = tempt.productAsin AND DATE_FORMAT(productprice.createdDate,'%Y-%m-%d') = tempt.tempDate
AND productprice.id < tempt.maxId)tempout) 
;