mysql - 删除重复行，按 max(datetime) 保留最近的行

Question

下面的 MySQL 语句根据 ADDRESS1 和 POSTCODE 选择所有重复的行，其中最近的行 INSPECTION_DATE。本质上，它会选择我想要保留的所有内容。

SELECT COUNT(*), MAX(INSPECTION_DATE), ADDRESS1, POSTCODE, id
FROM epc 
GROUP BY ADDRESS1, POSTCODE 
HAVING COUNT(*) > 1;

我的问题是，如何删除所有旧的重复项，保留上述语句返回的那些。

Answer 1

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));