如何获取每个room的最新rooms_message关联消息及回复？

查询需求 #

• 获取每个room对应的最新rooms_message记录
• 关联查询discussions_messages表中的text字段
• 同时关联查询对应discussions_messages的discussions_replies表中的回复内容

问题背景 #

尝试过GROUP BY结合MAX(created_at)的查询方式，但只能得到每个房间的最新消息时间，无法匹配到对应记录的正确id，进而导致关联的消息文本和回复内容全部错误。

数据表结构 #

rooms_messages表

discussions_messages表

discussions_replies表

原查询的问题 #

原查询通过GROUP BY room_uuid聚合，存在以下核心问题：

1. rm.uuid并非对应最新记录的uuid，分组后非聚合列的取值无确定性
2. 由此关联出的dm.text也不是最新消息的文本
3. 无法正确关联并加载对应最新消息的回复内容

正确查询方案 #

使用窗口函数ROW_NUMBER()对每个room_uuid下的记录按created_at倒序排序，取排序为1的记录（即最新一条），再关联其他表获取消息文本和回复内容：

WITH latest_room_messages AS (
    SELECT 
        rm.id,
        rm.room_uuid,
        rm.discussion_message_uuid,
        rm.created_at,
        ROW_NUMBER() OVER (PARTITION BY rm.room_uuid ORDER BY rm.created_at DESC) AS rn
    FROM rooms_messages rm
)
SELECT 
    lrm.id AS room_message_id,
    lrm.room_uuid,
    lrm.created_at,
    dm.text AS message_text,
    dr.text AS reply_text
FROM latest_room_messages lrm
INNER JOIN discussions_messages dm 
    ON lrm.discussion_message_uuid = dm.id
LEFT JOIN discussions_replies dr 
    ON dm.id = dr.discussion_message_uuid
WHERE lrm.rn = 1;

如果需要将同一消息的所有回复合并为一行（以MySQL为例），可使用字符串聚合函数：

WITH latest_room_messages AS (
    SELECT 
        rm.id,
        rm.room_uuid,
        rm.discussion_message_uuid,
        rm.created_at,
        ROW_NUMBER() OVER (PARTITION BY rm.room_uuid ORDER BY rm.created_at DESC) AS rn
    FROM rooms_messages rm
)
SELECT 
    lrm.id AS room_message_id,
    lrm.room_uuid,
    lrm.created_at,
    dm.text AS message_text,
    GROUP_CONCAT(dr.text SEPARATOR ', ') AS replies
FROM latest_room_messages lrm
INNER JOIN discussions_messages dm 
    ON lrm.discussion_message_uuid = dm.id
LEFT JOIN discussions_replies dr 
    ON dm.id = dr.discussion_message_uuid
WHERE lrm.rn = 1
GROUP BY lrm.id, lrm.room_uuid, lrm.created_at, dm.text;

说明 #

• ROW_NUMBER()窗口函数按room_uuid分组，每组内按created_at降序排列，标记每条记录的序号rn，rn=1即为该房间的最新消息
• 通过CTE先筛选出最新记录，再关联其他表，确保关联的都是对应最新消息的文本和回复
• 字符串聚合版本适合需要将同一消息的所有回复展示在一行的场景，可根据实际需求选择

内容的提问来源于stack exchange，提问作者Jérémie Chazelle