如何通过单条SQL查询获取包含最新消息的会话列表

现有表结构 #

conversation表 #

messages表 #

预期结果 #

此前我用两步实现：

• 获取所有会话列表
• 循环遍历每个会话，单独查询其最后一条消息

现在需要用单条SQL查询完成这个需求。

解决方案 #

方法1：使用窗口函数ROW_NUMBER() #

兼容PostgreSQL、MySQL 8.0+、SQL Server等主流数据库，通过窗口函数为每个会话的消息按时间倒序排序，取排序为1的最新消息：

SELECT 
    c.conversation_id,
    c.name,
    c.is_group,
    m.sender_id,
    m.text AS last_message_text,
    m.created_at
FROM conversation c
JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY created_at DESC) AS rn
    FROM messages
) m ON c.conversation_id = m.conversation_id
WHERE m.rn = 1;

方法2：关联子查询获取最新消息 #

如果数据库不支持窗口函数，可先通过子查询找到每个会话的最新消息时间，再关联回消息表获取内容：

SELECT 
    c.conversation_id,
    c.name,
    c.is_group,
    m.sender_id,
    m.text AS last_message_text,
    m.created_at
FROM conversation c
JOIN messages m ON c.conversation_id = m.conversation_id
WHERE m.created_at = (
    SELECT MAX(created_at) 
    FROM messages 
    WHERE conversation_id = c.conversation_id
);

注：若同一会话存在多条同时间的消息，上述方法会返回多条结果，可结合message_id确保只取一条：

SELECT 
    c.conversation_id,
    c.name,
    c.is_group,
    m.sender_id,
    m.text AS last_message_text,
    m.created_at
FROM conversation c
JOIN messages m ON c.conversation_id = m.conversation_id
WHERE (m.conversation_id, m.message_id) = (
    SELECT conversation_id, MAX(message_id)
    FROM messages 
    WHERE conversation_id = c.conversation_id
    GROUP BY conversation_id
);

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