mysql - MySQL获取按日期排序的唯一对话和最后一条消息

Question

我正在制作一个消息传递系统。我想检索特定用户的对话内容（该用户既可以是发送者也可以是接收者）按日期排序。我在网上查找了每个问题，但没有一个按预期工作。这是示例表数据。

+-----+---------+-------+---------+------------+
| id  |from_user|to_user| content |   msg_date |
+-----+---------+-------+---------+------------+
|  1  |    5    |    2  | test1   | 2019-12-01 |
|  2  |    2    |    5  | test2   | 2019-12-02 |
|  3  |    2    |    7  | test3   | 2019-12-03 |
|  4  |    2    |    7  | test4   | 2019-12-04 |
|  5  |    5    |    2  | test5   | 2019-12-05 |
|  6  |    7    |    2  | test6   | 2019-12-06 |
|  7  |    7    |    2  | test7   | 2019-12-07 |
|  8  |    5    |    2  | test8   | 2019-12-08 |
+-----+---------+-------+---------+------------+

这就是我所期望的。（假设特定用户 id 为 2）

+-----+---------+-------+---------+------------+
| id  |from_user|to_user| content |   msg_date |
+-----+---------+-------+---------+------------+
|  7  |    2    |    7  | test7   | 2019-12-07 |
|  8  |    5    |    2  | test8   | 2019-12-08 |
+-----+---------+-------+---------+------------+

谢谢

标签： mysqlsqlgreatest-n-per-group

Answer 1

您可以使用相关子查询进行过滤，该子查询提取每个对话的最新消息的日期：

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)
    )

DB Fiddle 上的演示

编号 | 来自用户 | to_user | 内容 | msg_date  
-: | --------: | ------: | :-------- | :---------
 7 | 7 | 2 | 测试7 | 2019-12-07
 8 | 5 | 2 | 测试8 | 2019-12-08