mysql - 如何从 mySQL 中的每个对话中获取最后一条消息？

Question

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

消息表:
ID-----Sender_ID-----Receiver_ID-----body--- --时间戳
1--------------------4--------------------2-------- ----...----------------...
2-------------------- 2--------------------4---------------------------- ----...
3--------------------4-------------------- --2----------------------------...
4-------- ----------4--------------------2------------...... --------------
5----------------4-------- ----------3----------------------------...
6--------------------3--------------------4-------- ----...--------------------...

结果:
ID-----Sender_ID-----Receiver_ID-----body--- --时间戳
4--------------------4--------------------2------ ----...----------------...
6-------------------- 3--------------------4---------------------------- ---...

Answer 1

要从对话中获取最后一条消息，您可以使用分组查询来获取最后一个 ID(如果它始终递增)或最后一个时间戳。 User_1 将是 session ID 较低的用户，user_2 是 session 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