MySQL Max of a Date 未返回正确的元组

2024-05-20 • 问答

我有一个“消息”表，用于存储一段时间内发送给人们的关于某些项目的消息。

messages表的结构是：

message_id 用户身份发送日期 created_at

对于每个用户，我可以在表中有多个元组。这些消息有些已经发送，有些还没有发送。

我正在尝试为每个用户获取最后创建的消息。我正在使用 max(created_at) 和 group_by(user_id)，但关联的 message_id 不是与 max(created_id) 元组关联的那个。

表格数据：

   message_id | user_id | date_sent | created_at
----------------------------------------------
    1           1       2021-07-01  2021-07-01        
    2           1       2021-07-02  2021-07-02
    3           2       2021-07-01  2021-07-01
    4           3       2021-07-04  2021-07-04
    5           1       2021-07-22  2021-07-22
    6           1       NULL        2021-07-23
    7           2       NULL        2021-07-29
    8           1       NULL        2021-07-29
    9           3       2021-07-29  2021-07-29

我的选择：

select * from messages ma right join 
( SELECT max(mb.created_at),message_id
FROM `messages` mb WHERE mb.created_at <= '2021-07-24' 
group by user_id)
mc on ma.message_id=mc.message_id

结果是

   message_id | user_id | date_sent | created_at
----------------------------------------------      
    5           1       2021-07-22  2021-07-23
    3           2       2021-07-01  2021-07-01
    4           3       2021-07-04  2021-07-04

我不知道为什么，但是对于用户 1，返回的 message_id 不是与具有 max(created_at) 的元组关联的那个。

我期望是：（获取按 user_id 分组的 select 的 max(date_sent) 元组）

   message_id | user_id | date_sent | created_at
----------------------------------------------      
    6           1       NULL        2021-07-23
    3           2       2021-07-01  2021-07-01
    4           3       2021-07-04  2021-07-04

有什么想法吗？有什么帮助吗？谢谢。

您在 MySQL 的 notorious nonstandard extension to GROUP BY 上磕磕绊绊。它给你一种错觉，你可以做你不能做的事情。示例

 SELECT max(created_at),message_id
   FROM messages 
  GROUP BY user_id

其实就是

 SELECT max(created_at),ANY_VALUE(message_id)
   FROM messages 
  GROUP BY user_id

其中 ANY_VALUE() 表示 MySQL 可以从该用户的消息中选择它认为最方便的任何 message_id。那不是你想要的。

要解决您的问题，您首先需要使用子查询为每个 created_at 查找最新的 user_id 日期。 Fiddle。

              SELECT user_id,MAX(created_at) created_at
                FROM messages
               WHERE created_at <= '2021-07-24'
               GROUP BY user_id

然后，您需要找到在该日期创建的特定 user_id 的消息。为此使用子查询。 Fiddle

  SELECT a.*
    FROM messages a
    JOIN (
              SELECT user_id,MAX(created_at) created_at
                FROM messages
               WHERE created_at <= '2021-07-24'
               GROUP BY user_id
         ) b ON a.user_id = b.user_id AND a.created_at = b.created_at

看看 JOIN 是如何工作的？它为每个用户提取与最新日期匹配的行。

有一个可能的优化。如果

您的 message_id 是一个自动递增的主键，并且
您从不更新 created_at 列，而仅在插入行时将它们设置为当前日期

那么每个user_id的最新消息也是message_id最大的消息。在这种情况下，您可以改用此查询。 Fiddle

  SELECT a.*
    FROM messages a
    JOIN (
              SELECT user_id,MAX(message_id) message_id
                FROM messages
               WHERE created_at <= '2021-07-24'
               GROUP BY user_id
         ) b ON a.message_id=b.message_id

由于主键索引的工作方式，这可以更快。

这里你需要一个普通的 JOIN 而不是 RIGHT 或 LEFT JOIN：普通的 JOIN 只返回匹配 ON 条件的行。

专业提示几乎没有人真正使用 RIGHT JOIN。当您想要那种 JOIN 时，请使用 LEFT JOIN。您不希望这种连接来解决这个问题。

MySQL Max of a Date 未返回正确的元组

messages表的结构是：

kyb620 回答：MySQL Max of a Date 未返回正确的元组

大家都在问