如何用SQL按分组取最大日期更新指定条件的数据？

原始数据 #

操作需求 #

• 步骤1：插入编号为1的记录
• 步骤2：利用编号为2的记录修改编号1的日期字段
• 仅针对flag = "y"的记录，用同组内日期最新的记录（编号2）更新编号1的日期，最终仅保留编号1和3的记录

期望最终结果 #

实现方案 #

可以实现，按以下逐行操作逻辑执行即可：

1. 插入编号1的记录 #

INSERT INTO your_table (no, `group`, date, value, flag)
VALUES (1, 'a', '2022-10-13', 'old', 'y');

2. 插入编号2的记录 #

INSERT INTO your_table (no, `group`, date, value, flag)
VALUES (2, 'a', '2022-10-15', 'new', 'y');

3. 更新编号1的日期字段 #

利用编号2的日期值更新，同时限定flag='y'的条件：

UPDATE your_table t1
SET t1.date = (SELECT t2.date FROM your_table t2 WHERE t2.no = 2 AND t2.flag = 'y')
WHERE t1.no = 1 AND t1.flag = 'y';

如果要通用适配同组最新日期（而非指定编号2），可以用子查询获取同组flag='y'的最新日期：

UPDATE your_table t1
JOIN (
    SELECT `group`, MAX(date) AS latest_date
    FROM your_table
    WHERE flag = 'y'
    GROUP BY `group`
) t2 ON t1.`group` = t2.`group`
SET t1.date = t2.latest_date
WHERE t1.no = 1 AND t1.flag = 'y';

4. 删除编号2的记录 #

DELETE FROM your_table WHERE no = 2;

5. 处理分组b的记录 #

• 插入编号3的记录：

INSERT INTO your_table (no, `group`, date, value, flag)
VALUES (3, 'b', '2022-01-01', 'old', 'n');

• 插入编号4的记录后直接删除（因为flag='n'无需更新）：

INSERT INTO your_table (no, `group`, date, value, flag)
VALUES (4, 'b', '2022-01-03', 'new', 'n');
DELETE FROM your_table WHERE no = 4;

执行以上步骤后即可得到期望的最终结果。

内容的提问来源于stack exchange，提问作者younghyun