MySQL如何查询可互换收发ID配对分组的最新消息并按时间倒序排列

你现有查询的问题有两个：

1. 仅按from_id分组，完全没有处理from_id和to_id互换的会话配对逻辑，也没有关联接收方ID
2. 非严格SQL模式下，GROUP BY后返回的是分组内第一条匹配记录，而非按message_time排序后的最新记录，逻辑不符合需求。

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

用LEAST和GREATEST函数归一化双向会话标识，配合ROW_NUMBER窗口函数取每个会话最新记录：

WITH ranked_messages AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            -- 把双向配对归一为相同分组，比如1-4和4-1会分到同一组
            PARTITION BY LEAST(from_id, to_id), GREATEST(from_id, to_id) 
            ORDER BY message_time DESC
        ) AS rn
    FROM messages
)
SELECT message_time, from_id, to_id, message
FROM ranked_messages
WHERE rn = 1
ORDER BY message_time DESC;

执行结果完全匹配你给出的预期输出。

方案2：MySQL 5.x 兼容方案 #

如果不支持窗口函数，可以先查每个会话的最新时间，再关联回原表取完整记录：

SELECT m.*
FROM messages m
INNER JOIN (
    SELECT 
        LEAST(from_id, to_id) AS min_id,
        GREATEST(from_id, to_id) AS max_id,
        MAX(message_time) AS latest_time
    FROM messages
    GROUP BY min_id, max_id
) t ON LEAST(m.from_id, m.to_id) = t.min_id 
    AND GREATEST(m.from_id, m.to_id) = t.max_id 
    AND m.message_time = t.latest_time
ORDER BY m.message_time DESC;

如果存在同一会话同一时间有多条消息的场景，可在查询末尾额外加GROUP BY t.min_id, t.max_id过滤。

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