你需要先得到每个房间的最后一条消息，然后加入其余的表：

SELECT r.id
      ,r.type
      ,r.name
      ,rm.text
FROM rooms r
INNER JOIN 
(
      SELECT participant_id, MAX(id) AS max_id
      FROM room_messages 
      GROUP BY participant_id
) rm_max 
    ON rm.id = rm_max.participant_id 
INNER JOIN room_messages rm
    ON r.id = rm.participant_id
    AND rm_max.max_id = rm.id
ORDER BY rm.chat_room_id;

稍微调整了一下

SELECT r.id
      ,r.type
      ,r.name
      ,rm.text
      ,rmf.file
FROM rooms r
LEFT JOIN room_participants rp
    on r.id = rp.room_id
INNER JOIN
(
      SELECT room_id, MAX(id) AS max_id
      FROM room_messages
      GROUP BY room_id
) rm_max
    ON r.id = rm_max.room_id
INNER JOIN room_messages rm
    ON r.id = rm.room_id AND rm_max.max_id = rm.id
LEFT JOIN room_message_files rmf
    ON rm.id = rmf.room_message_id
where rp."participantType" = 'USER' and rp.participant_id = 1
ORDER BY rm.room_id;