如何选择最后创建的记录在一个组由子句在mysql?

Question

我使用mysql 8.0.23

我有三个表，chats,chat_users和chat_messages

我想选择chat_id，最后的消息(与最大createdAt日期为特定组)。换句话说，消息顺序为created_at(组内)，from_user_id值，用于id为1的用户为成员的所有聊天。

sql和ddl表如下

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);

到目前为止，我尝试的查询不能正常工作是

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字段，尽管我试图按max(chm.created_at) desc排序。这个顺序由group by子句在我认为分组之后执行，而不是在组中的项目中执行。我知道我可以在select语句中选择最大日期但我想选择组内的最后内容值而不是选择max(ch。created_at)作为last_created_at_msg_within_group

我不知道如何从具有最高chm的项目中选择content字段。通过c.id

分组，从组内创建created_at示例测试数据

chats

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

chats_users

1 1 1
2 1 2
3 2 1 
4 2 2
5 3 1
6 3 2

chat_messages

1 1 1 lastmsg 2021-07-28 21:50:31
1 1 2 themsg  2021-07-23 20:51:01

这种情况下的逻辑应该返回

chat_id  content   from_user_id
1        lastmsg   1

PS:在这里发帖之前，我做了我的功课，并在论坛上研究了类似的问题，但他们试图从一个组中获得最后插入的行，并且不像我的。

Answer 1

以下是我为MySQL 8.0提供的窗口函数解决方案:

select * from (
select
c.id as chat_id,
content,
chm.from_user_id,
chm.created_at,
row_number() over (partition by c.id order by chm.created_at desc) as rownum
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
) as t
where rownum = 1;