mysql - 从SQL获取当前用户和其他人之间的最后一条消息

我想检索当前用户和其他用户之间的最后一条消息，并将其显示在用户的消息页面中，就像facebook一样。我用这个

<pre>
select *
from ch_messages
where receiver='$current_user_id' or sender='$current_user_id' && (least(sender, receiver), greatest(sender, receiver), f_msg_date)
in
(
    select
       least(sender, receiver) as x, greatest(sender, receiver) as y,
       max(f_msg_date) as date
    from ch_messages
    group by sender, receiver
)
</pre>

但是它将所有消息从一个用户发送到当前用户，并将当前用户的最后一条消息发送给该用户。
I want it like this image

这是我的表结构

<pre>
mid  => messages id
sender  => The sender of the message
receiver => The receiver of the message
msg   => The message sent
f_msg_date => date in which the message was sent
</pre>

最佳答案

您只需要删除子查询中接收方周围的引号，
假设每个消息都具有唯一的id列（自动递增），如果您希望最后一条消息的话

  select *
  from ch_messages
  where receiver='$current_user_id'
  or sender='$current_user_id'
  AND ( id, least(sender, receiver), greatest(sender, receiver), f_msg_date )
  in
  (  select
           max(id)
         , least(sender, receiver)
         , greatest(sender, receiver),
           max(f_msg_date)
      from ch_messages
      group by sender, receiver
  )

关于mysql - 从SQL获取当前用户和其他人之间的最后一条消息，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40822246/