按Item ID筛选用户收发最新聊天消息的SQL查询求助

你的SQL查询只返回单行数据的原因是分组逻辑错误：原查询里的IF(sender = 2, sender, receiver)会把所有和用户2相关的聊天都归到同一组（分组键都是2），自然只能得到一条最大ID的记录。要实现每个对话（不管你是发送方还是接收方）的最新消息，需要先为每个对话生成唯一的分组标识。

修正方案1：修正分组逻辑（适用于所有版本SQL） #

使用LEAST()和GREATEST()函数，把对话双方的ID按大小排序后作为分组键，这样不管消息是2→3还是3→2，都会被归到同一个对话组里：

SELECT id,
       `sender`,
       `receiver`,
       `message`, 
       `created_at`
FROM chats
WHERE id IN (
    SELECT MAX(id) 
    FROM chats
    WHERE `sender` = 2 OR `receiver` = 2
    -- 生成每个对话的唯一分组标识
    GROUP BY LEAST(`sender`, `receiver`), GREATEST(`sender`, `receiver`)
)
ORDER BY `created_at` DESC;

修正方案2：使用窗口函数（适用于MySQL 8+、PostgreSQL等支持窗口函数的数据库） #

用ROW_NUMBER()窗口函数直接为每个对话的消息按时间倒序编号，取每组第一条（最新消息），逻辑更直观：

SELECT id, `sender`, `receiver`, `message`, `created_at`
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY LEAST(`sender`, `receiver`), GREATEST(`sender`, `receiver`)
               ORDER BY `created_at` DESC
           ) AS rn
    FROM chats
    WHERE `sender` = 2 OR `receiver` = 2
) t
WHERE rn = 1
ORDER BY `created_at` DESC;

这两种方案都能返回每个对话的最新一条消息，满足聊天列表展示的需求。

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