如何用SQL获取指定用户信使收件箱的各会话最新消息？

你的问题核心在于分组逻辑错误：仅用receiver_id分组无法正确识别同一个对话的双向消息（比如用户1和3的对话，当1是发送方时receiver_id是3，当3是发送方时receiver_id是1，这会被分成两个独立分组），导致你要么漏掉关键会话的最新消息，要么拿到无关结果。

解决方案：按对话双方的唯一组合分组 #

我们需要用对话双方的ID生成一个唯一的分组标识，不管消息的发送方向。可以用LEAST()和GREATEST()函数（大多数主流数据库如MySQL、PostgreSQL、SQLite都支持），它们会分别返回两个ID中的较小值和较大值，确保同一个对话的所有消息都被归为一组。

修正后的SQL如下：

SELECT * 
FROM chats AS tm 
WHERE tm.id IN (
    SELECT MAX(id) 
    FROM chats 
    WHERE sender_id = 1 OR receiver_id = 1
    GROUP BY LEAST(sender_id, receiver_id), GREATEST(sender_id, receiver_id)
)

代码解释 #

1. 过滤用户参与的消息：子查询中WHERE sender_id = 1 OR receiver_id = 1先筛选出用户1所有发送或接收的消息
2. 按对话分组：GROUP BY LEAST(sender_id, receiver_id), GREATEST(sender_id, receiver_id)将同一个对话的双向消息归为一组
3. 取最新消息：MAX(id)获取每个对话中ID最大的记录（假设ID是自增的，代表最新消息）
4. 获取完整消息：外层查询根据子查询得到的最新消息ID，取出完整的消息内容

兼容不支持LEAST()/GREATEST()的数据库 #

如果你的数据库不支持这两个函数（比如旧版SQL Server），可以用CASE语句替代：

SELECT * 
FROM chats AS tm 
WHERE tm.id IN (
    SELECT MAX(id) 
    FROM chats 
    WHERE sender_id = 1 OR receiver_id = 1
    GROUP BY 
        CASE WHEN sender_id < receiver_id THEN sender_id ELSE receiver_id END,
        CASE WHEN sender_id < receiver_id THEN receiver_id ELSE sender_id END
)

这个方案会准确返回用户1参与的每个对话的最新消息，不会漏掉像ID3所在的会话，也不会包含无关的记录。

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