如何让SQL查询结果按最新消息排首且同MessageChainId消息归组

开发聊天系统时，需要从数据库查询消息并实现以下效果：

• 同MessageChainId的消息归组展示
• 最新的消息组排在最前面
• 组内消息也按最新在前排序

当前SQL语句 #

SELECT nc.MessageChainId, notifications.reciverId, notifications.id 
    FROM notifications 
    INNER JOIN notificationscontent AS nc ON notifications.notificationId = nc.id 
    INNER JOIN users AS u ON notifications.senderId = u.id
    INNER JOIN user_tokens AS ut ON ut.user_id = u.id
    WHERE notifications.reciverId = 
    (SELECT user_id 
    FROM user_tokens 
    WHERE user_token = ?)
    ORDER BY notifications.id DESC, MessageChainId

当前查询结果 #

期望查询结果 #

解决方案 #

方法1：使用窗口函数（推荐，适用于MySQL 8+、PostgreSQL等支持窗口函数的数据库）

通过窗口函数MAX()计算每个消息链的最新消息ID，以此作为组排序的核心依据，组内再按消息ID降序排列：

SELECT 
    nc.MessageChainId, 
    notifications.reciverId, 
    notifications.id
FROM notifications 
INNER JOIN notificationscontent AS nc ON notifications.notificationId = nc.id 
INNER JOIN users AS u ON notifications.senderId = u.id
INNER JOIN user_tokens AS ut ON ut.user_id = u.id
WHERE notifications.reciverId = (
    SELECT user_id 
    FROM user_tokens 
    WHERE user_token = ?
)
ORDER BY 
    MAX(notifications.id) OVER (PARTITION BY nc.MessageChainId) DESC,
    notifications.id DESC;

方法2：子查询关联（兼容低版本数据库）

先通过子查询获取每个消息链的最新消息ID，再关联主查询完成排序逻辑：

SELECT 
    nc.MessageChainId, 
    n.reciverId, 
    n.id
FROM notifications n
INNER JOIN notificationscontent nc ON n.notificationId = nc.id 
INNER JOIN users u ON n.senderId = u.id
INNER JOIN user_tokens ut ON ut.user_id = u.id
INNER JOIN (
    SELECT 
        nc.MessageChainId, 
        MAX(n.id) AS max_chain_id
    FROM notifications n
    INNER JOIN notificationscontent nc ON n.notificationId = nc.id
    WHERE n.reciverId = (SELECT user_id FROM user_tokens WHERE user_token = ?)
    GROUP BY nc.MessageChainId
) chain_max ON nc.MessageChainId = chain_max.MessageChainId
WHERE n.reciverId = (SELECT user_id FROM user_tokens WHERE user_token = ?)
ORDER BY chain_max.max_chain_id DESC, n.id DESC;

两种方法均可实现需求：让同MessageChainId的消息归组展示，最新的消息组排在最前面，组内消息也按最新在前排序。

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