如何从mySQL中的每个会话中获得最后一条消息？

Question

我想要的东西：

我正在寻找一个SELECT查询，它允许我从每个会话中选择最后一个消息。会话由具有相同用户ID的所有行组成，用于双方。例子: 4-2和2-4。

消息表：

ID-----Sender_ID-----Receiver_ID-----body-----timestamp

1-------------------4---------------------2----------...-----------------...

2-------------------2---------------------4----------...-----------------...

3-------------------4---------------------2----------...-----------------...

4-------------------4---------------------2----------...-----------------...

5-------------------4---------------------3----------...-----------------...

6-------------------3---------------------4----------...-----------------...

结果：

ID-----Sender_ID-----Receiver_ID-----body-----timestamp

4-------------------4---------------------2----------...-----------------...

6-------------------3---------------------4----------...-----------------...

Answer 1

要从会话中获取最后一条消息，可以使用group查询获取最后一个ID (如果它总是递增的话)或最后一个时间戳。User_1将是id较低的用户，而user_2则是会话id较高的用户：

select
  least(sender_ID, receiver_ID) as user_1,
  greatest(sender_ID, receiver_ID) as user_2,
  max(ID) as last_id,
  max(timestamp) as last_timestamp
from
  messages
group by
  least(sender_ID, receiver_ID),
  greatest(sender_ID, receiver_ID)

然后，您可以通过这样的查询获得实际消息：

select m.*
from
  messages m inner join (
    select
      least(sender_ID, receiver_ID) as user_1,
      greatest(sender_ID, receiver_ID) as user_2,
      max(ID) as last_id,
      max(timestamp) as last_timestamp
    from
      messages
    group by
      least(sender_ID, receiver_ID),
      greatest(sender_ID, receiver_ID)
  ) s on least(sender_id, receiver_id)=user_1
         and greatest(sender_id, receiver_id)=user_2
         and m.id = s.last_id -- or last timestamp