MySQL执行update left join时如何选取右表Time taken最大值的对应记录

实现思路 #

你当前的SQL会匹配右表所有符合关联条件的记录，无法自动筛选Time taken最大值对应的条目，需要先对右表按Id、Receiver分组，过滤出每组内Time taken最大的唯一记录后再关联更新。

适配全MySQL版本写法 #

UPDATE tableA a
LEFT JOIN (
    -- 筛选每个Id+Receiver分组下Time taken最大的记录
    SELECT b1.*
    FROM tableB b1
    INNER JOIN (
        SELECT Id, Receiver, MAX(`Time taken`) AS max_time
        FROM tableB
        GROUP BY Id, Receiver
    ) b2 ON b1.Id = b2.Id AND b1.Receiver = b2.Receiver AND b1.`Time taken` = b2.max_time
) b ON a.Id = b.Id AND a.Receiver = b.Receiver
SET a.guid = b.guid, a.item = b.item
WHERE a.guid IS NULL;

MySQL 8.0+ 窗口函数简化写法 #

UPDATE tableA a
LEFT JOIN (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Id, Receiver ORDER BY `Time taken` DESC) AS rn
    FROM tableB
) b ON a.Id = b.Id AND a.Receiver = b.Receiver AND b.rn = 1
SET a.guid = b.guid, a.item = b.item
WHERE a.guid IS NULL;

注意事项 #

• 带空格的字段名Time taken需要用反引号包裹，避免触发语法错误
• 注意字段名拼写对齐，你原SQL里写的items和表结构实际的item字段名不一致，需要修正
• 若存在同一分组下多条Time taken相同的最大记录，可在窗口函数的ORDER BY后追加排序规则（比如按guid倒序），保证匹配结果唯一

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