带分组的 SQL SELECT

Question

我需要从 2 个用户之间的每次对话中选择最后一条记录

我的表:

id | sender_id | recipient_id | message
1     2           3              msg_1 
2     3           2              msg_2
4     2           3              msg_4
5     2           3              msg_5
7     4           3              msg_7
8     3           4              msg_8

选择后它应该找到这个记录

id | sender_id | recipient_id | message 
5     2           3              msg_5
8     3           4              msg_8

Answer 1

SELECT  m.*
FROM    mytable m
WHERE   id = ( SELECT   MAX(id)
           FROM     mytable
           WHERE    ( sender_id = m.sender_Id
                      OR recipient_id = m.sender_id
                    )
                    AND ( recipient_id = m.recipient_Id
                          OR sender_id = m.recipient_id
                        )
         )