mysql - 在 MySQL 中的 JOIN 中进行排序 - 每个用户的最新消息

Question

得到一个查询的两个版本。也不是我需要的..

尝试返回所有用户，以及最近发送给该用户或者或从该用户的消息的时间。另一个标志显示最后一条消息是否来自当前行中的用户(因此他们是否“等待响应”)。

最后，需要对整个结果集进行排序，让这些用户首先“等待响应”，然后按最新消息的时间排序。

版本 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

然而，应该强调的是，虽然这可能会起作用，但不能保证起作用 - 分组选择语句中包含的未聚合、未分组的值通常是那些首先按顺序排列，但 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