MySQL多个连接得到一个结果集(用mysql max函数)

我尝试使用max（..）从集合中获取最大值，但我也想获取与这一行对应的其他行值（如果有多行匹配相同的“max”，则为多行）。我使用MySQL 8。
我能够部分地使用这个SQL（SelVazi在这篇相关文章中提供的解决方案：使用SELECT查询从MySQL中的聚合函数（max）的同一行获取值）
但是，现在事情变得更复杂了，我将任务拆分到一个单独的表中，这样我只需要在“user_tasks”表中保存任务ID值。
在有多个任务具有相同的“max”日期时间的情况下，我希望在结果集中列出所有这些任务名称（带有task_duration值）
这是我目前使用的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;

字符串
下面是DB模式和一些示例值：

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);

# please note below there are duplicate date values (so both are "max")

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);

# and now, the tasks table...

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

型
上面的SELECT语句产生以下结果：

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

型
另外，如果有人能为这篇文章想出一个更好的标题，请建议一个，我很乐意编辑它以更好地反映这个问题。谢谢！

加入到另一张table上...

SELECT
    u.user_id,
    u.login_time,
    tasks.task_name,
    u.task_duration 
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
----------------------------------------
-- Added this
----------------------------------------
INNER JOIN 
  tasks 
    ON tasks.task_id = u.task_id;

字符串
或者更准确地说...

WITH
  ranked_user_task AS
(
  SELECT
    *,
    DENSE_RANK() OVER (
      PARTITION BY user_id
          ORDER BY login_time DESC
    ) 
      AS user_task_rank
  FROM
    user_tasks
)
SELECT
    u.user_id,
    u.login_time,
    t.task_name,
    u.task_duration 
FROM
  ranked_user_task   AS u
INNER JOIN 
  tasks              AS t
    ON t.task_id = u.task_id
WHERE
      u.user_id IN (123, 456, 789)  
  AND u.user_task_rank = 1
;

型

MySQL多个连接得到一个结果集(用mysql max函数)

1条答案

相关问题

热门标签

最新问答