如何合并SQL查询实现用户列表按最后发送消息时间排序？

场景1：按用户最后收到当前用户（ID=57）的消息时间排序 #

如果你的需求是按其他用户最后接收来自ID57的消息时间排序，可以用LEFT JOIN关联用户表和消息表，分组后取每个用户对应的最晚消息时间，再排序：

SELECT a.username
FROM accounts a
LEFT JOIN messages m 
  ON m.outgoing_msg_id = 57 AND m.incoming_msg_id = a.id
WHERE a.id != 57 AND a.activation_code = 'activated'
GROUP BY a.id, a.username
ORDER BY MAX(m.msg_date) DESC, a.username ASC;

• 用LEFT JOIN是为了保留那些还没收到ID57消息的用户，这类用户的消息时间会是NULL，排序时默认排在最后；如果只想保留有过消息交互的用户，把LEFT JOIN改成INNER JOIN即可。
• 末尾的a.username ASC是排序的兜底规则，当多个用户的最后消息时间相同时，按用户名升序排列。

场景2：按双方之间最后一条消息的时间排序（无论消息方向） #

如果需要按每个用户和ID57之间最后一次交互的消息时间（不管是ID57发的还是对方发的）排序，调整关联条件即可：

SELECT a.username
FROM accounts a
LEFT JOIN messages m 
  ON (m.outgoing_msg_id = 57 AND m.incoming_msg_id = a.id)
  OR (m.outgoing_msg_id = a.id AND m.incoming_msg_id = 57)
WHERE a.id != 57 AND a.activation_code = 'activated'
GROUP BY a.id, a.username
ORDER BY MAX(m.msg_date) DESC, a.username ASC;

这个查询会把双方互发的消息都纳入统计，取每组的最晚时间作为排序依据，逻辑更贴合聊天应用中"最近联系"的排序需求。

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