如何删除MySQL中的记录并保持最新日期 [英] How do I delete records in MySQL and keep the lastest date

问题描述

示例table_1

ID Email         Answer   UpdateDate
1  [e-mail]   1        2011-07-02
2  [e-mail]   3        2011-07-11
3  [e-mail]   3        2011-07-12
4  [e-mail]   5        2011-07-13
5  [e-mail]   5        2011-07-14
6  [e-mail]   4        2011-07-14
7  [e-mail]   4        2011-07-14
8  [e-mail]   4        2011-07-15

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

How do I delete this records but keep the latest UpdateDate

结果:

ID Email         Answer   UpdateDate
4  [e-mail]   5        2011-07-13
7  [e-mail]   4        2011-07-14
8  [e-mail]   4        2011-07-15

推荐答案

您可以使用临时变量存储最高日期，然后使用单独的查询删除所有<比起那个来说.请记住，变量是特定于连接的.

You could use a temporary variable to store the highest date and then a seperate query to delete everything that is < than that. Remember that variables are connection specific.

select max(UpdateDate) from table_1 into @TempUpdateDate

delete from table_1 where UpdateDate < @TempUpdateDate

这篇关于如何删除MySQL中的记录并保持最新日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！