如何在查询中添加子查询以显示房间内的最后一条消息？

方法一：通过聚合子查询获取最新消息 #

先通过子查询找出每个房间的最新消息ID（假设room_messages.id为自增主键，值越大消息越新），再关联回消息表拿到具体内容，同时保留原查询的筛选逻辑：

SELECT 
    r.id AS 房间ID,
    r.name AS 房间名称,
    rm.text AS 最后一条消息内容,
    rm.is_read AS 消息已读状态
FROM rooms AS r
LEFT JOIN room_participants rp ON r.id = rp.room_id
-- 子查询：获取每个房间的最新消息ID
LEFT JOIN (
    SELECT room_id, MAX(id) AS 最新消息ID
    FROM room_messages
    GROUP BY room_id
) latest_msg ON r.id = latest_msg.room_id
-- 关联到具体消息记录
LEFT JOIN room_messages rm ON latest_msg.最新消息ID = rm.id
WHERE rp."participantType" = 'USER' AND rp.participant_id = 1;

方法二：使用窗口函数筛选最新消息 #

通过ROW_NUMBER()窗口函数对每个房间的消息按ID倒序排序，取排序后的第一条即为最新消息：

SELECT 
    房间ID,
    房间名称,
    最后一条消息内容,
    消息已读状态
FROM (
    SELECT 
        r.id AS 房间ID,
        r.name AS 房间名称,
        rm.text AS 最后一条消息内容,
        rm.is_read AS 消息已读状态,
        -- 按房间分组，消息ID降序排序
        ROW_NUMBER() OVER (PARTITION BY r.id ORDER BY rm.id DESC) AS 排序序号
    FROM rooms AS r
    LEFT JOIN room_participants rp ON r.id = rp.room_id
    LEFT JOIN room_messages rm ON r.id = rm.room_id
    WHERE rp."participantType" = 'USER' AND rp.participant_id = 1
) AS 房间消息汇总
WHERE 排序序号 = 1;

注：假设room_messages表包含room_id字段（消息必然归属某个房间，推测原表结构描述遗漏该字段），否则无法关联到对应聊天室。

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