在MySQL中的JOIN中订购-每个用户的最新消息

Question

得到一个查询的两个版本。两者都不符合我的需要。。

正在尝试返回所有用户，以及将发送到该用户的OR的最新消息的时间。另一个标志显示最后一条消息是否是来自当前行中用户的（因此他们是否在"等待响应"）。

最后，需要对整个结果集进行排序，使这些用户首先"等待响应"，然后在最新消息发布时进行排序。

版本1:

SELECT `users`.*,
MAX(`messages`.`time_sent`) AS `_message_time_sent`,
(`messages`.`user_id` = `users`.`id`) AS `_message_awaiting_response` 
FROM `users` LEFT JOIN `messages` 
ON (`messages`.`user_id` = `users`.`id` OR `messages`.`to_user_id` = `users`.`id`) 
GROUP BY `users`.`id` 
ORDER BY `_message_awaiting_response` DESC, `messages`.`time_sent` DESC

版本1的问题："_message_sent_time"是正确的（通过MAX），但"_message_awaiting_response"标志始终源自MAX（）发生之前联接中最先出现的消息，该消息可能不是最近的消息。

版本2:

SELECT DISTINCT `users`.*,
`messages`.`time_sent` AS `_message_time_sent`,
(`messages`.`user_id` = `users`.`id`) AS `_message_awaiting_response`
FROM `users` LEFT JOIN `messages`
ON (`messages`.`user_id` = `users`.`id` OR `messages`.`to_user_id` = `users`.`id`) 
GROUP BY `users`.`id` 
ORDER BY `_message_awaiting_response` DESC, `messages`.`time_sent` DESC

对于版本2，问题基本相同——由于没有MAX（）聚合函数，DISTINCT会挑出联接中发现的第一条消息（不是最近的消息）。

有没有一种简单的方法可以确保最新消息在这里成为目标？

附言：它需要在MySQL 4中工作；）

Answer 1

最简单的方法应该是：

SELECT * FROM
(SELECT `users`.*,
        `messages`.`time_sent` AS `_message_time_sent`,
        (`messages`.`user_id` = `users`.`id`) AS `_message_awaiting_response` 
 FROM `users` 
 LEFT JOIN `messages` 
        ON `messages`.`user_id` = `users`.`id` OR 
           `messages`.`to_user_id` = `users`.`id`
 ORDER BY `users`.`id`, `messages`.`time_sent` DESC
) V
GROUP BY V.`id` 
ORDER BY `_message_awaiting_response` DESC, `_message_time_sent` DESC

然而，需要强调的是，尽管可能会起作用，但不能保证能起作用——分组select语句中包含的未聚合、未分组的值通常是按顺序排列的值，但MySQL文档并没有将这种行为描述为可靠的。

一个应该始终有效的替代方案（假设给定用户没有多条具有相同时间戳的消息）是：

select u.*,
       m.`time_sent` AS `_message_time_sent`,
       (m.`user_id` = u.`id`) AS `_message_awaiting_response` 
from `users` u
left join (select `user_id`, max(`time_sent`) AS `last_sent`
           from (select `time_sent`, `user_id` from `messages` union all
                 select `time_sent`, `to_user_id` `user_id` from `messages`) v
           group by `user_id`) lm
       on u.`user_id` = lm.`user_id`
left join `messages` m
       ON m.`time_sent` = lm.`last_sent` and 
          lm.`user_id` in (m.`user_id`, m.`to_user_id`)
ORDER BY `_message_awaiting_response` DESC, `_message_time_sent` DESC