MySQL 8中使用max()聚合函数时如何获取对应行的其他字段值

你的原查询存在逻辑问题：当使用GROUP BY user_id时，task_name属于非聚合、非分组字段，在MySQL关闭ONLY_FULL_GROUP_BY模式下，会随机选取分组内某一行的task_name值，而非对应max(login_time)的那一行的值，这就是结果错误的原因。

针对MySQL 8，推荐以下两种正确的实现方式：

方法一：使用窗口函数ROW_NUMBER()（推荐） #

利用MySQL 8支持的窗口函数，给每个用户的记录按login_time倒序排序，取排序后行号为1的记录，即为该用户最新登录对应的任务：

SELECT user_id, login_time AS last_login_time, task_name AS last_task_worked_on
FROM (
    SELECT 
        user_id,
        login_time,
        task_name,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_time DESC) AS rn
    FROM user_tasks
    WHERE user_id IN (123, 456, 789)
) t
WHERE rn = 1;

方法二：子查询关联获取对应行 #

先通过子查询获取每个用户的最大登录时间，再关联原表匹配对应的任务名称：

SELECT ut.user_id, ut.login_time AS last_login_time, ut.task_name AS last_task_worked_on
FROM user_tasks ut
JOIN (
    SELECT user_id, MAX(login_time) AS max_login
    FROM user_tasks
    WHERE user_id IN (123, 456, 789)
    GROUP BY user_id
) mt ON ut.user_id = mt.user_id AND ut.login_time = mt.max_login
WHERE ut.user_id IN (123, 456, 789);

以上两种方法都能得到你预期的正确结果：

'123','2023-05-30 06:10:03','feed dog'
'456','2023-03-30 06:10:03','feed dog'
'789','2023-05-30 06:10:03','bathe dog'

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