您可以使用关联的子查询进行筛选,该子查询提取每个会话的最新消息的日期:
select t.*
from mytable t
where
2 in (from_user, to_user)
and t.msg_date = (
select max(t1.msg_date)
from mytable t1
where
least(t1.from_user, t1.to_user) = least(t.from_user, t.to_user)
and greatest(t1.from_user, t1.to_user) = greatest(t.from_user, t.to_user)
)
Demo on DB Fiddle
id | from_user | to_user | content | msg_date
-: | --------: | ------: | :------ | :---------
7 | 7 | 2 | test7 | 2019-12-07
8 | 5 | 2 | test8 | 2019-12-08