查询特定用户与每个联系人的最后一次对话

12 浏览
0 Comments

查询特定用户与每个联系人的最后一次对话

我知道已经有类似问题的提问，比如这个。但是它们对我来说似乎不起作用。

我想做的事情：

我想获取给定用户每个联系人的最后一条消息。

问题

个人消息表：

+-----------+---------------------+---------------------+-------------+
| sender_id | text                | created_date        | receiver_id |
+-----------+---------------------+---------------------+-------------+
|        16 | hello               | 2020-11-22 22:37:40 |          15 |
|        15 | hi                  | 2020-11-22 22:37:55 |          16 |
|        15 | how are you ?       | 2020-11-22 22:38:18 |          16 |
|        18 | hey                 | 2020-11-22 22:40:12 |          16 |
|        16 | you there ?         | 2020-11-22 22:40:32 |          18 |
|        15 | where are you ?     | 2020-11-22 23:50:23 |          18 |
|        15 | can we talk         | 2020-11-22 23:50:38 |          18 |
|        18 | how is life ?       | 2020-11-22 23:53:32 |          15 |
|        18 | whats up            | 2020-11-22 23:54:38 |          15 |
+-----------+---------------------+---------------------+-------------+

通过上面的表格，我们可以看到id为15的用户与id为16和18的用户进行了对话。

我想要的是每个对话的最后一条消息，所以对于用户15和16之间的对话，最后一条消息将是：

+-----------+---------------------+---------------------+-------------+
| sender_id | text                | created_date        | receiver_id |
+-----------+---------------------+---------------------+-------------+
|        15 | how are you ?       | 2020-11-22 22:38:18 |          16 |
+-----------+---------------------+---------------------+-------------+

类似地，15和18之间的对话的最后一条消息将是：

+-----------+---------------------+---------------------+-------------+
| sender_id | text                | created_date        | receiver_id |
+-----------+---------------------+---------------------+-------------+
|        18 | whats up            | 2020-11-22 23:54:38 |          15 |
+-----------+---------------------+---------------------+-------------+

到目前为止我的查询：：

select * from personal_message where receiver_id in 
   (
     select receiver_id from personal_message where receiver_id in 
       (
         select receiver_id from 
         personal_message where receiver_id = 15
       ) order by created_date desc
   ) group by sender_id;

我尝试了许多查询，我都记不起来了。

0

1 答案

0 Comments

问题的出现原因：

用户想要获取特定用户与每个联系人的最后一次对话，但是使用了不同版本的MySQL数据库或ORM工具，导致无法使用第一个查询方法。

解决方法：

第一种方法使用了MySQL 8+版本的ROW_NUMBER窗口函数，可以根据最近的对话时间对消息进行排序，并使用LEAST和GREATEST函数对发送者和接收者进行分组，然后选择每个组的第一条记录作为结果。

第二种方法使用了连接操作，适用于早期版本的MySQL或ORM工具。首先对消息进行分组，获取每个组中最大的创建时间，然后通过连接操作获取每个组中的对应记录作为结果。

但是第二种方法中存在一个错误，将on子句中的receiver_id拼写错误，导致报错。需要将代码复制并重新运行。

用户尝试了自己的查询方法，但没有获得正确的结果，并请求帮助。然而，由于时间限制，回答者无法立即调试并提供解决方案。

最后，用户表示理解，并同意等待回答者有时间后再提供解决方案。

0