我用的是mysql 8.0.23
我有三张桌子,chats
个、chat_users
和chat_messages
个
我想 Select chat_id,最后一条消息(具有特定组的最大创建日期.换言之,对于ID为1的用户是成员的所有聊天,消息ORDER BY CREATED_AT DEC),from_USER_ID值.
表SQL和DDLS如下所示
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);
到目前为止,我try 的查询不能正常工作
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
字段,尽管我try 按max(chm.created_at)desc进行排序.我认为这个ORDER BY AFTER GROUP BY子句是在分组之后执行的,而不是在组中的项内执行.
我知道我可能可以在SELECT语句中 Select 最大日期,但我想 Select 组内的最后一个内容值,而不是 Select max(ch.created_at)作为last_Created_at_msg_in_group
我不知道如何从我使用c.id分组的组内具有最高chm.created_at的项中 Select content
字段
测试数据示例
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: 在这里发帖之前,我在论坛上做了功课,研究了类似的问题,但他们试图从一个组中获得最后插入的行,与我的不同.