双人私密聊天系统：获取各对话的最新消息

嘿，针对你开发的双人私密聊天系统里，要获取每个对话最后一条消息的需求，我整理了几个实用的SQL解决方案，你可以根据自己用的数据库类型和性能需求来选：

方案一：窗口函数（推荐，适用于MySQL 8.0+、PostgreSQL、SQL Server等现代数据库） #

这个方案逻辑清晰，性能表现也很出色，核心是先把双向的对话统一成一个分组标识，再用窗口函数给每个分组内的消息按时间倒序排名，取排名第一的就是最新消息。

WITH conversation_groups AS (
    SELECT
        message_id,
        sender_id,
        receiver_id,
        message_text,
        date,
        -- 生成统一的对话ID：把两个用户ID按大小排序后拼接，确保双向对话归为同一组
        CONCAT(LEAST(sender_id, receiver_id), '-', GREATEST(sender_id, receiver_id)) AS conversation_id,
        -- 按对话分组，按消息时间倒序分配排名
        ROW_NUMBER() OVER (
            PARTITION BY CONCAT(LEAST(sender_id, receiver_id), '-', GREATEST(sender_id, receiver_id))
            ORDER BY date DESC
        ) AS rn
    FROM chat
)
SELECT
    message_id,
    sender_id,
    receiver_id,
    message_text,
    date,
    conversation_id
FROM conversation_groups
WHERE rn = 1;

优化建议：给表建立复合索引(sender_id, receiver_id, date DESC)和(receiver_id, sender_id, date DESC)，能让窗口函数的分组排序更快执行。

方案二：关联子查询（适用于不支持窗口函数的老版本数据库） #

如果你的数据库版本比较旧（比如MySQL 5.x），可以用子查询先找到每个对话的最新消息时间，再关联原表获取对应消息：

SELECT c1.*
FROM chat c1
WHERE date = (
    SELECT MAX(date)
    FROM chat c2
    -- 匹配同一对话的双向消息
    WHERE (c2.sender_id = c1.sender_id AND c2.receiver_id = c1.receiver_id)
       OR (c2.sender_id = c1.receiver_id AND c2.receiver_id = c1.sender_id)
)
-- 确保每个对话只返回一条结果
GROUP BY CONCAT(LEAST(c1.sender_id, c1.receiver_id), '-', GREATEST(c1.sender_id, c1.receiver_id));

注意：如果同一对话在同一时间有多条消息，这个方案会通过GROUP BY取第一条，你可以根据需求调整逻辑。

方案三：PostgreSQL专属简洁方案（DISTINCT ON） #

如果你用的是PostgreSQL，DISTINCT ON语法能让代码更简洁，直接保留每个分组的第一条记录：

SELECT DISTINCT ON (LEAST(sender_id, receiver_id), GREATEST(sender_id, receiver_id))
    message_id,
    sender_id,
    receiver_id,
    message_text,
    date
FROM chat
-- 先按对话分组，再按时间倒序，确保第一条是最新消息
ORDER BY LEAST(sender_id, receiver_id), GREATEST(sender_id, receiver_id), date DESC;

这个写法非常直观，PostgreSQL会自动帮你处理分组后的第一条记录筛选。

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