如何按sender_id和receiver_id分组获取各组合的最新行？

问题原因 #

你原来的SQL无法得到最新行，是因为GROUP BY在未指定聚合规则的情况下，会返回每组的第一行数据（通常是msg_id最小的旧记录），后续的ORDER BY仅对最终结果排序，无法改变分组时选取的行。

解决方案1：使用窗口函数（MySQL 8.0+推荐） #

利用ROW_NUMBER()窗口函数，按会话分组后给每行标记排序号，取排序第一的最新行：

WITH ranked_messages AS (
    SELECT 
        msg_id, 
        sender_id, 
        receiver_id, 
        msg, 
        media_link, 
        sent_time, 
        received_time, 
        msg_type, 
        is_seen,
        -- 按sender_id+receiver_id分组，按发送时间倒序、msg_id倒序排序
        ROW_NUMBER() OVER (
            PARTITION BY sender_id, receiver_id 
            ORDER BY sent_time DESC, msg_id DESC
        ) AS rn
    FROM messaging
    WHERE sender_id = 10 OR receiver_id = 10
)
SELECT 
    msg_id, 
    sender_id, 
    receiver_id, 
    msg, 
    media_link, 
    sent_time, 
    received_time, 
    msg_type, 
    is_seen
FROM ranked_messages
WHERE rn = 1;

说明：

• PARTITION BY sender_id, receiver_id：将数据按会话（发送者+接收者）分组
• ORDER BY sent_time DESC, msg_id DESC：确保同一会话内最新发送的消息排在最前，若同一时间有多条消息，取msg_id最大的那条
• 筛选rn=1即可得到每个会话的最新行

解决方案2：兼容MySQL 5.x版本（子查询关联） #

如果你的MySQL版本低于8.0，可通过子查询先获取每个会话的最新时间和最大msg_id，再关联原表取完整数据：

SELECT m.*
FROM messaging m
INNER JOIN (
    -- 先找出每个会话的最新发送时间和最大msg_id
    SELECT 
        sender_id, 
        receiver_id, 
        MAX(sent_time) AS latest_sent_time,
        MAX(msg_id) AS latest_msg_id
    FROM messaging
    WHERE sender_id = 10 OR receiver_id = 10
    GROUP BY sender_id, receiver_id
) AS latest ON 
    m.sender_id = latest.sender_id 
    AND m.receiver_id = latest.receiver_id 
    AND m.sent_time = latest.latest_sent_time
    AND m.msg_id = latest.latest_msg_id
WHERE m.sender_id = 10 OR m.receiver_id = 10;

说明：

• 子查询通过MAX(sent_time)和MAX(msg_id)锁定每个会话的最新记录标识
• 关联原表时同时匹配时间和msg_id，避免同一时间多条消息导致的歧义

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