下面的MySQL语句根据ADDRESS1和邮政编码选择所有具有重复项的行,其中最近的行是INSPECTION_DATE。本质上,它选择了我想要保留的所有的。
SELECT COUNT(*), MAX(INSPECTION_DATE), ADDRESS1, POSTCODE, id
FROM epc
GROUP BY ADDRESS1, POSTCODE
HAVING COUNT(*) > 1;我的问题是,如何删除所有旧的副本,保留上述语句返回的副本。
发布于 2018-12-18 16:11:07
http://sqlfiddle.com/#!9/8de866/1
DELETE e
FROM epc e
INNER JOIN epc max_
ON e.address1 = max_.address1
AND e.postcode = max_.postcode
AND e.inspection_date < max_.inspection_date;更新 http://sqlfiddle.com/#!9/5bd5981/1
DELETE e
FROM epc e
INNER JOIN epc max_
ON e.address1 = max_.address1
AND e.postcode = max_.postcode
AND (e.inspection_date < max_.inspection_date
OR (e.inspection_date = max_.inspection_date
AND e.id < max_.id));https://stackoverflow.com/questions/53835813
复制相似问题