修正MySQL语句以获取每个聊天室的唯一最新消息

问题的核心在于，当多条消息的message_date完全相同时，原SQL的ORDER BY子句没有提供唯一的排序依据，导致变量@rn的行号计算逻辑无法正确区分同聊天室（friend_id）下的消息顺序，最终出现多个rn=1的结果。

解决方案：添加唯一排序字段 #

我们只需要在ORDER BY中加入message表的id字段（按降序排序，假设id是自增主键，最新的消息id更大），确保同时间的消息有明确的先后顺序，这样行号计算逻辑就能正确为每个聊天室标记唯一的rn=1记录。

修改后的完整SQL语句如下：

SELECT * FROM ( 
    SELECT t.*, 
           @rn := if(@friend = t.friend_id, @rn + 1, if( @friend := t.friend_id, 1, 1) ) as rn 
    FROM ( 
        SELECT m.id, -- 新增id字段用于排序
               m.message, 
               m.message_read, 
               m.message_date, 
               CASE WHEN m.sender = 4 THEN m.receiver ELSE m.sender END as friend_id, 
               CASE WHEN m.sender = 4 THEN p2.nickname ELSE p1.nickname END as name, 
               CASE WHEN m.sender = 4 THEN p2.image ELSE p1.image END as image 
        FROM message as m 
        JOIN profile as p1 ON m.sender = p1.user_id -- sender 
        JOIN profile as p2 ON m.receiver = p2.user_id -- receiver 
        WHERE 4 IN (m.sender, m.receiver) 
    ) as t 
    CROSS JOIN ( SELECT @rn := 0, @friend := 0 ) as var 
    ORDER BY t.friend_id, t.message_date desc, t.id desc -- 新增id desc排序
) q 
WHERE q.rn = 1 

改动说明 #

1. 在子查询t中新增了m.id字段，用于后续排序；
2. 在最内层的ORDER BY中添加了t.id desc，确保同friend_id、同message_date的消息中，id最大的（最新插入的）消息排在最前面；
3. 行号变量@rn会为每个friend_id下的第一条消息标记为rn=1，后续同聊天室的消息rn会递增，最终通过WHERE q.rn=1过滤出每个聊天室的唯一最新消息。

验证效果 #

针对你提供的测试数据，执行修改后的SQL会得到你期望的结果：每个friend_id仅返回一条最新消息。即使新增了id=5的同时间消息，也只会保留id最大的那条，符合需求。

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