mysql - 我怎样才能得到两个用户之间聊天对话的最后记录？

Question

=> 我想根据创建日期字段获取两个用户之间聊天转换的最后记录，以获取最后一条消息。下面是我的表格

我已尝试通过此查询获取聊天转换的最后一条消息的结果，但无法获得正确的结果。

select `chat_messages`.`id`, `chat_messages`.`message`, `chat_messages`.`created_at` 
from `chat_messages`
where `chat_messages`.`is_group` = 0 
and `chat_messages`.`deleted_at` is null 
and (`sender_id` =141 or `client_id` = 141)
group by `chat_messages`.`message_token` 
order by `chat_messages`.`created_at` desc

=> 当前结果:-

=> 应该是:-

注意:-此功能与聊天功能相同，我不想只获得一条记录。

谢谢。

Answer 1

group by 导致了您的问题。您需要做的就是对 desc 进行排序，然后将结果集限制为一行

select `chat_messages`.`id`, `chat_messages`.`message`, `chat_messages`.`created_at` 
from `chat_messages`
where `chat_messages`.`is_group` = 0 
and `chat_messages`.`deleted_at` is null 
and (`sender_id` =141 or `client_id` = 141)
order by `chat_messages`.`created_at` desc
LIMIT 1