MySQL多表关联结合MAX函数获取对应完整结果集

问题 #

使用MySQL 8，需获取每个用户最大登录时间对应的所有任务行，并关联tasks表展示任务名称及task_duration值。现有SQL已能筛选出最大登录时间的行，但缺少任务名称，需调整以关联任务表。

当前使用的SQL语句：

SELECT u.*
FROM user_tasks u
INNER JOIN (
  SELECT user_id, max(login_time) as last_login_time, task_duration 
  FROM user_tasks
  WHERE user_id IN (123, 456, 789) 
  group by user_id
) AS s ON s.user_id = u.user_id and s.last_login_time = u.login_time;

数据库结构及示例数据 #

create table user_tasks (
    user_id int(11), 
    login_time datetime, 
    task_id int(11), 
    task_duration int(11)
)

create table tasks (
    task_id int(11),
    task_name varchar(50)
)

insert into user_tasks values (123, '2023-01-30 06:10:03', 1, 50);
insert into user_tasks values (123, '2023-02-25 06:10:03', 2, 45);

# 以下两行登录时间相同，均为该用户的最大登录时间
insert into user_tasks values (123, '2023-05-30 06:10:03', 3, 60);
insert into user_tasks values (123, '2023-05-30 06:10:03', 2, 20);

insert into user_tasks values (456, '2023-03-30 06:10:03', 3, 50);
insert into user_tasks values (456, '2023-02-25 06:10:03', 1, 20);
insert into user_tasks values (456, '2023-01-30 06:10:03', 2, 10);

insert into user_tasks values (789, '2023-03-30 06:10:03', 3, 35);
insert into user_tasks values (789, '2023-01-30 06:10:03', 1, 38);
insert into user_tasks values (789, '2023-05-30 06:10:03', 2, 26);

insert into user_tasks values (898, '2023-05-30 06:10:03', 1, 16);
insert into user_tasks values (900, '2023-05-30 06:10:03', 2, 18);

# tasks表数据
insert into tasks values (1, 'walk dog');
insert into tasks values (2, 'bathe dog');
insert into tasks values (3, 'feed dog');

当前查询结果 #

123,'2023-05-30 06:10:03', 3, 60
123,'2023-05-30 06:10:03', 2, 20
456,'2023-03-30 06:10:03', 3, 50
789,'2023-05-30 06:10:03', 2, 26

期望结果 #

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

解决方案 #

优化原有SQL，关联tasks表获取任务名称，并修正子查询的不合理字段：

SELECT u.user_id, u.login_time, t.task_name, u.task_duration
FROM user_tasks u
INNER JOIN (
  SELECT user_id, MAX(login_time) AS last_login_time
  FROM user_tasks
  WHERE user_id IN (123, 456, 789)
  GROUP BY user_id
) AS s ON u.user_id = s.user_id AND u.login_time = s.last_login_time
LEFT JOIN tasks t ON u.task_id = t.task_id;

关键调整说明 #

1. 子查询优化：子查询仅保留user_id和最大登录时间last_login_time，避免分组时引入非聚合、非分组字段（原SQL中的task_duration）导致的结果不确定性，同时符合MySQL 8严格模式（ONLY_FULL_GROUP_BY）的要求；
2. 关联任务表：通过LEFT JOIN关联tasks表，用user_tasks.task_id匹配tasks.task_id，获取对应的任务名称；
3. 字段筛选：明确指定需要返回的字段，与期望结果的结构完全匹配。

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