如何在MySQL的GROUP BY子句中选取分组内最新创建的记录

环境与表结构 #

使用mysql 8.0.23，涉及三张业务表：chats（聊天组表）、chat_users（用户-聊天组关联表）、chat_messages（聊天消息表），表结构SQL定义如下：

create table chats
(
    id         int unsigned auto_increment primary key,
    created_at timestamp default CURRENT_TIMESTAMP not null
);

create table if not exists chat_users
(
    id      int unsigned auto_increment
        primary key,
    chat_id    int unsigned not null,
    user_id int unsigned not null,
    constraint chat_users_user_id_chat_id_unique
        unique (user_id, chat_id),
    constraint chat_users_chat_id_foreign
        foreign key (chat_id) references chats (id)
);

create index chat_users_chat_id_index
    on chat_users (chat_id);

create index chat_users_user_id_index
    on chat_users (user_id);


create table chat_messages
(
    id       int unsigned auto_increment primary key,
    chat_id  int unsigned                            not null,
    from_user_id int unsigned                            not null,
    content      varchar(500) collate utf8mb4_unicode_ci not null,
    created_at   timestamp default CURRENT_TIMESTAMP     not null,
    constraint chat_messages_chat_id_foreign
        foreign key (chat_id) references chats (id)
);

create index chat_messages_chat_id_index
    on chat_messages (chat_id);
    
create index chat_messages_from_user_id_index
    on chat_messages (from_user_id);

核心需求 #

查询ID为1的用户所属所有聊天组的chat_id、该组内最新创建的消息内容以及消息发送者的from_user_id字段。

尝试的错误查询 #

SET @userId = 1;
select
       c.id as chat_id,
       content,
       chm.from_user_id
from chat_users
         inner join chats c on chat_users.chat_id = c.id
         inner join chat_messages chm on c.id = chm.chat_id
where chat_users.user_id = @userId
group by c.id
order by c.id desc, max(chm.created_at) desc

问题分析 #

上述查询无法正确返回最新消息的content：GROUP BY逻辑是先分组再排序，无法保证分组内取到created_at最大的那条记录的内容；仅用max(chm.created_at)只能拿到最大时间值，无法关联对应消息的content和from_user_id。

测试数据 #

• chats表：

id  created_at
1   2021-07-23 20:51:01
2   2021-07-23 20:51:01
3   2021-07-23 20:51:01

• chat_users表：

id  chat_id  user_id
1   1        1
2   1        2
3   2        1 
4   2        2
5   3        1
6   3        2

• chat_messages表：

id  chat_id  from_user_id  content   created_at
1   1        1             lastmsg   2021-07-28 21:50:31
2   1        2             themsg    2021-07-23 20:51:01

预期结果 #

chat_id  content   from_user_id
1        lastmsg   1

解决方案 #

利用MySQL 8.0支持的窗口函数ROW_NUMBER()，按聊天组分组并按消息创建时间倒序排序，取每组的第一条记录：

SET @userId = 1;

WITH ranked_messages AS (
    SELECT 
        cm.chat_id,
        cm.content,
        cm.from_user_id,
        ROW_NUMBER() OVER (PARTITION BY cm.chat_id ORDER BY cm.created_at DESC) AS rn
    FROM chat_messages cm
    JOIN chat_users cu ON cm.chat_id = cu.chat_id
    WHERE cu.user_id = @userId
)
SELECT chat_id, content, from_user_id
FROM ranked_messages
WHERE rn = 1;

逻辑说明 #

1. 先通过chat_users筛选出用户ID为1的所有聊天组，关联chat_messages获取这些组的全部消息
2. 使用ROW_NUMBER()窗口函数，按chat_id分组，每组内按created_at倒序给消息排名，最新消息的排名为1
3. 最后筛选出排名为1的记录，即为每个聊天组的最新消息

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