mysql - 如何删除MySQL中的记录并保持最新日期

Question

示例 table_1

ID Email         Answer   UpdateDate
1  xxx.@xx.com   1        2011-07-02
2  xxx.@xx.com   3        2011-07-11
3  vvv.@xx.com   3        2011-07-12
4  vvv.@xx.com   5        2011-07-13
5  xxx.@xx.com   5        2011-07-14
6  xxx.@xx.com   4        2011-07-14
7  xxx.@xx.com   4        2011-07-14
8  zzz.@xx.com   4        2011-07-15

如何删除此记录但保留最新的 UpdateDate

结果:

ID Email         Answer   UpdateDate
4  vvv.@xx.com   5        2011-07-13
7  xxx.@xx.com   4        2011-07-14
8  zzz.@xx.com   4        2011-07-15

Answer 1

我会根据相关子查询检查 UpdateDate。

CREATE TEMPORARY TABLE
  latestRecord (
    Email        VARCHAR(128),
    updateDate   DATETIME
) 
INSERT INTO 
  latestRecord
SELECT
  Email,
  MAX(updateDate) AS updateDate
FROM
  table_1
GROUP BY
  Emal

DELETE 
  table_1
FROM
  table_1
INNER JOIN
  latestRecord
    ON  latestRecord.Email      = table_1.Email
    AND latestRecord.updateDate < table_1.updateDate

编辑

同样逻辑的另一个重构