MySQL中Group By与Order By返回错误列的问题修复

原始数据表 #

期望结果 #

以用户77为当前用户，按聊天对象分组，获取每组最新的消息，结果如下：

尝试的SQL语句 #

SELECT (CASE WHEN from_user = 77 THEN to_user ELSE from_user END) AS other_user, 
       MAX(msg_id) as id, 
       msg 
FROM chat_schema 
WHERE 77 IN (from_user, to_user) 
GROUP BY other_user 
ORDER BY id DESC;

错误结果 #

执行后返回的msg列与对应msg_id不匹配，分组后获取的是分组内第一条消息，而非最大msg_id对应的消息：

修复方案 #

方法1：子查询关联匹配最大msg_id #

先分组得到每个聊天对象对应的最新消息ID，再关联原表获取对应消息内容：

SELECT t.msg_id, t.msg, 
       (CASE WHEN t.from_user = 77 THEN t.to_user ELSE t.from_user END) AS other_user
FROM chat_schema t
JOIN (
    SELECT 
        (CASE WHEN from_user = 77 THEN to_user ELSE from_user END) AS other_user,
        MAX(msg_id) AS max_msg_id
    FROM chat_schema
    WHERE 77 IN (from_user, to_user)
    GROUP BY other_user
) AS latest ON t.msg_id = latest.max_msg_id
ORDER BY t.msg_id DESC;

方法2：窗口函数（适用于MySQL 8+、PostgreSQL等） #

用ROW_NUMBER()按聊天对象分组，按消息ID降序排序，取每组第一条记录：

SELECT msg_id, msg, other_user
FROM (
    SELECT 
        msg_id,
        msg,
        (CASE WHEN from_user = 77 THEN to_user ELSE from_user END) AS other_user,
        ROW_NUMBER() OVER (PARTITION BY (CASE WHEN from_user = 77 THEN to_user ELSE from_user END) ORDER BY msg_id DESC) AS rn
    FROM chat_schema
    WHERE 77 IN (from_user, to_user)
) AS ranked
WHERE rn = 1
ORDER BY msg_id DESC;

方法3：关联子查询（通用型） #

直接在WHERE子句中筛选出每个聊天对象对应的最新消息记录：

SELECT 
    msg_id,
    msg,
    (CASE WHEN from_user = 77 THEN to_user ELSE from_user END) AS other_user
FROM chat_schema t1
WHERE 77 IN (from_user, to_user)
AND msg_id = (
    SELECT MAX(msg_id)
    FROM chat_schema t2
    WHERE (t2.from_user = t1.from_user AND t2.to_user = t1.to_user)
       OR (t2.from_user = t1.to_user AND t2.to_user = t1.from_user)
)
ORDER BY msg_id DESC;

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