MySQL关联表分组查询：如何筛选各组最新消息数据？

我正在开发聊天列表界面API，需要向客户端返回聊天对象的名称（user.name）和最新消息内容（message.content）。编写的MySQL查询语句如下，但当message表新增数据时，无法选中最新的message.content：

select u.name, m.content
FROM chat_room as c
    INNER JOIN message as m on m.sender_no = c.user_type_2 or c.user_type_2 = m.reciver_no
    INNER JOIN user as u on u.user_no= c.user_type_2
WHERE c.user_type_1 = 7
GROUP BY u.name

相关表数据 #

message表 #

message
--------
message_no|chat_room_no|sender_no|reciver_no|content       | timestamp
------------------------------------------------------------------------
1         |1           |7        |8         |test message1 | 2022-08-30 18:00
2         |1           |8        |7         |test message2 | 2022-08-30 19:00
3         |2           |7        |9         |test message3 | 2022-08-30 20:00
4         |2           |7        |9         |test message4 | 2022-08-30 21:00
5         |3           |7        |10        |test message5 | 2022-08-30 22:00

chat_room表 #

chat_room
--------
chat_room_no|user_type_1|user_type_2|
---------------------------------------
1           |7          |8          |
2           |7          |9          |
3           |7          |10         |

user表 #

user
--------
user_no|name        |
-----------------
7      |testuser7   |
8      |testuser8   |
9      |testuser9   |
10     |testuser10  |

当前查询结果 #

0|name        |content
---------------------------
1|testuser10  |test message1
2|testuser9   |test message3
3|testuser8   |test message1

期望结果（获取最新消息数据） #

0|name        |content
-------------------------
1|testuser10  |test message5
2|testuser9   |test message4
3|testuser8   |test message2

解决方案 #

问题分析 #

1. 关联条件错误：原查询通过sender_no/reciver_no和user_type_2的OR条件关联message和chat_room，会把所有与user_type_2相关的消息都关联，忽略了消息所属的聊天房间（chat_room_no），导致关联逻辑混乱。
2. GROUP BY逻辑问题：GROUP BY u.name后，content字段未使用聚合函数，MySQL会随机返回分组内的一条记录，无法保证是最新消息。

正确查询方法 #

方法一：使用窗口函数（MySQL 8.0+支持）

通过ROW_NUMBER()窗口函数按聊天房间分组，取每组内时间最新的消息：

SELECT u.name, m.content
FROM chat_room c
JOIN user u ON u.user_no = c.user_type_2
JOIN (
    SELECT 
        chat_room_no,
        content,
        ROW_NUMBER() OVER (PARTITION BY chat_room_no ORDER BY timestamp DESC) AS rn
    FROM message
) m ON m.chat_room_no = c.chat_room_no AND m.rn = 1
WHERE c.user_type_1 = 7;

方法二：子查询获取最新消息时间

先找到每个聊天房间的最新消息时间，再关联对应的消息记录：

SELECT u.name, m.content
FROM chat_room c
JOIN user u ON u.user_no = c.user_type_2
JOIN message m ON m.chat_room_no = c.chat_room_no
WHERE c.user_type_1 = 7
AND m.timestamp = (
    SELECT MAX(timestamp) 
    FROM message 
    WHERE chat_room_no = c.chat_room_no
);

两种方法都能正确关联聊天房间与消息，确保返回每个聊天对象对应的最新消息内容。

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