Example table_1
ID Email Answer UpdateDate
1 [email protected] /cdn-cgi/l/email-protection 1 2011-07-02
2 [email protected] /cdn-cgi/l/email-protection 3 2011-07-11
3 [email protected] /cdn-cgi/l/email-protection 3 2011-07-12
4 [email protected] /cdn-cgi/l/email-protection 5 2011-07-13
5 [email protected] /cdn-cgi/l/email-protection 5 2011-07-14
6 [email protected] /cdn-cgi/l/email-protection 4 2011-07-14
7 [email protected] /cdn-cgi/l/email-protection 4 2011-07-14
8 [email protected] /cdn-cgi/l/email-protection 4 2011-07-15
如何删除这条记录但保留最新的UpdateDate
结果是:
ID Email Answer UpdateDate
4 [email protected] /cdn-cgi/l/email-protection 5 2011-07-13
7 [email protected] /cdn-cgi/l/email-protection 4 2011-07-14
8 [email protected] /cdn-cgi/l/email-protection 4 2011-07-15
我会根据相关子查询检查 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
EDIT
相同逻辑的另一个重构
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)