MySQL查询获取会话最后一条消息及排序优化问题

要实现获取每个会话的最后一条消息并按消息ID降序排序，你可以先通过分组筛选出每个会话的最大messageId，再关联message表拿到对应消息的完整内容，最后整合到原有查询中。

完整SQL语句如下：

SELECT dt.conversationId, users.*, msg.*
FROM (
    SELECT DISTINCT 
        CASE WHEN user1_id = 1 THEN user2_id ELSE user1_id END userID,
        conversationId 
    FROM conversations 
    WHERE 1 IN (user2_id, user1_id)
) dt 
INNER JOIN users ON dt.userID = users.id
INNER JOIN (
    -- 筛选每个会话的最后一条消息ID
    SELECT conversationId, MAX(messageId) AS last_message_id
    FROM message
    GROUP BY conversationId
) last_msg ON dt.conversationId = last_msg.conversationId
INNER JOIN message msg ON last_msg.last_message_id = msg.messageId
ORDER BY msg.messageId DESC;

关键说明 #

• 子查询last_msg：通过GROUP BY conversationId对会话分组，用MAX(messageId)提取每个会话的最后一条消息ID，避免返回该会话的所有消息。
• 关联message表：通过last_message_id = msg.messageId匹配到对应消息的完整数据。
• 排序：最后用ORDER BY msg.messageId DESC实现结果按消息ID降序排列。

如果不需要message表的全部字段，可将msg.*替换为你需要的具体字段（比如msg.content, msg.send_time）。

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