基于用户ID获取聊天应用各对话最新消息记录方案咨询

针对你开发聊天应用时，需要按对话用户维度展示最新消息的需求——也就是从和你对话的10位用户中，每个用户只取双方最后一条消息（不管谁发的），我整理了两种实用的解决方案，适配不同的数据库环境：

这种方法逻辑清晰，性能也不错，适合支持SQL窗口函数的数据库。核心思路是把双向对话（比如你发对方、对方发你）归为同一个分组，然后给每个分组内的消息按时间倒序排名，取排名第一的那条就是最新消息。

假设你的消息表是messages，字段包括id（消息ID）、sender_id（发送者ID）、recipient_id（接收者ID）、content（消息内容）、created_at（发送时间）；用户表是users，字段有id、name。下面是具体查询代码：

WITH ranked_messages AS (
    SELECT
        -- 确定对话的对方ID
        CASE
            WHEN sender_id = '当前用户ID' THEN recipient_id
            ELSE sender_id
        END AS chat_partner_id,
        -- 关联用户表拿到对方名称
        u.name AS chat_partner_name,
        m.content,
        m.created_at,
        -- 按对话组排名，最新消息排第1
        ROW_NUMBER() OVER (
            PARTITION BY LEAST(sender_id, recipient_id), GREATEST(sender_id, recipient_id)
            ORDER BY m.created_at DESC, m.id DESC
        ) AS rn
    FROM messages m
    JOIN users u ON u.id = CASE
        WHEN m.sender_id = '当前用户ID' THEN m.recipient_id
        ELSE m.sender_id
    END
    -- 只筛选和当前用户相关的消息
    WHERE m.sender_id = '当前用户ID' OR m.recipient_id = '当前用户ID'
)
SELECT chat_partner_id, chat_partner_name, content, created_at
FROM ranked_messages
WHERE rn = 1
ORDER BY created_at DESC;

关键细节说明： #

• LEAST(sender_id, recipient_id)和GREATEST(sender_id, recipient_id)：把你和对方的ID按大小排序，确保双向对话被归为同一个分组（比如你ID是1，对方是2，不管是1→2还是2→1，分组键都是(1,2)）。
• ORDER BY m.created_at DESC, m.id DESC：如果两条消息在同一毫秒发送，用消息ID倒序确保取唯一的最新消息。

如果你的项目用的是不支持窗口函数的旧版数据库，可以用这种分组查询的方式，先拿到每个对话组的最新消息时间，再匹配对应的消息内容。

SELECT
    CASE
        WHEN m.sender_id = '当前用户ID' THEN m.recipient_id
        ELSE m.sender_id
    END AS chat_partner_id,
    u.name AS chat_partner_name,
    m.content,
    m.created_at
FROM messages m
JOIN users u ON u.id = CASE
    WHEN m.sender_id = '当前用户ID' THEN m.recipient_id
    ELSE m.sender_id
END
WHERE (LEAST(m.sender_id, m.recipient_id), GREATEST(m.sender_id, m.recipient_id), m.created_at) IN (
    -- 子查询：拿到每个对话组的最新消息时间
    SELECT
        LEAST(sender_id, recipient_id),
        GREATEST(sender_id, recipient_id),
        MAX(created_at)
    FROM messages
    WHERE sender_id = '当前用户ID' OR recipient_id = '当前用户ID'
    GROUP BY LEAST(sender_id, recipient_id), GREATEST(sender_id, recipient_id)
)
ORDER BY m.created_at DESC;

优化建议： #

如果消息表数据量很大，一定要给sender_id、recipient_id、created_at这三个字段创建联合索引，能大幅提升查询速度。

不管用哪种方案，最终都能实现你要的效果：只展示和你对话的10位用户，每个用户对应一条双方的最新消息，完美解决“从最后两条记录中仅取一条”的需求。

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