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

Question

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

信息表：
ID ----- SENDER_ID ----- Receiver_ID ----- 体 ----- 时间戳
1 ------------------- 4 --------------------- 2 -------- --... ----------------- ...
2 ------------------- 2 --- ------------------ 4 ----------...-----------------。 ..
3 ------------------- 4 --------------------- 2 ---- ------...-----------------。 ..
4 ------------------- 4 --------------------- 2 ---- ------... ----------------- ...
5 ------------------- 4 --------------------- 3 ----------...-------------- --- ...
6 ------------------- 3 --------------------- 4 ----------... ----------------- ...

结果：
ID --- - SENDER_ID ----- Receiver_ID ----- 体 ----- 时间戳
4 ------------------- 4 --------------------- 2 -------- ----... ----------------- ...
6 ------------------- 3- -------------------- 4 ----------...---------------- -...

Answer 1

要从对话中获取最后一条消息，可以使用通过查询的组来获取最后一个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