mysql 重复值取大id_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)

;