时间戳

首页 > 解决方案 > MySQL：从相互关联的 2 个表中获取最新消息

mysql - MySQL：从相互关联的 2 个表中获取最新消息

问题描述

首先，非常感谢您的帮助。

我有 2 个表：一个conversation表和一个message表，以及assoc_message__conversation将消息与对话相关联的第三个表。

我需要为每个指定的对话获取最新的message_id并message发送，以及与之conversation_id关联的对话。

这是一个 db-fiddle：https ://www.db-fiddle.com/f/kxRQeGUYYgQ7FTwi96hbLp/0

正如您在此示例中看到的，有两个对话conversation_id1 和 2，并且每个对话关联了三条消息。消息 1、2 和 3 与会话 1 关联，消息 4、5 和 6 与会话 2 关联。

我需要能够conversation_id在表中指定 's assoc_message__conversation（ID 1 和 2），并为指定的每个对话检索最新message_id的message和conversation_id从表中发送的相关信息。message

所以它应该拉的行是：

conversation_id | message_id | message
------------------------------------------------
              1 |          3 | "Latest message"
------------------------------------------------
              2 |          6 | "Latest message"
------------------------------------------------

非常感谢你的帮助！

标签： mysqlsql

解决方案

在旧版本的 MySQL (< 8.0.2) 中，我们可以使用Derived Tables。在派生表中，我们可以获得send_datetime每个conversation_id. 此外，值得注意的是，您可以在WHERE此子查询的子句中为 conversation_id 提供过滤器。

然后，我们可以使用此子查询的结果集并适当地连接回主表，以获取与对话中最新消息对应的行。

架构（MySQL v5.7）

在 DB Fiddle 上查看

查询 #1

SELECT
  amc.conversation_id, 
  m.message_id, 
  m.message   
FROM 
  assoc_message__conversation AS amc
JOIN message AS m 
  ON m.message_id = amc.message_id 
JOIN 
(
  SELECT
    amc1.conversation_id, 
    MAX(m1.send_datetime) AS latest_send_datetime
  FROM
   assoc_message__conversation AS amc1
  JOIN message AS m1
    ON m1.message_id = amc1.message_id 
  WHERE amc1.conversation_id IN (1,2)  -- Here you provide your input filters
  GROUP BY amc1.conversation_id
) AS dt  
  ON dt.conversation_id = amc.conversation_id AND 
     dt.latest_send_datetime = m.send_datetime;

结果

| conversation_id | message_id | message        |
| --------------- | ---------- | -------------- |
| 1               | 3          | Latest message |
| 2               | 6          | Latest message |

在 MySQL 8.0.2 及以上版本中，我们可以使用Row_Number()功能。在 的分区内conversation_id，我们将确定每条消息的行号，按 的降序排序send_datetime。在此子查询中，您可以在WHERE子句中为 conversation_id 提供过滤器。

然后，我们将此结果集用作派生表，并仅考虑那些行号值为 1 的行（因为它将属于 latest send_datetime）。

架构 (MySQL v8.0)

在 DB Fiddle 上查看

查询 #2

SELECT 
  dt.conversation_id, 
  dt.message_id, 
  dt.message 
FROM 
(
  SELECT
    amc.conversation_id, 
    m.message_id, 
    m.message, 
    ROW_NUMBER() OVER (PARTITION BY amc.conversation_id 
                       ORDER BY m.send_datetime DESC) AS row_no 
  FROM
   assoc_message__conversation AS amc
  JOIN message AS m
    ON m.message_id = amc.message_id 
  WHERE amc.conversation_id IN (1,2)  -- Here you provide your input filters
) AS dt  
WHERE dt.row_no = 1;

结果

| conversation_id | message_id | message        |
| --------------- | ---------- | -------------- |
| 1               | 3          | Latest message |
| 2               | 6          | Latest message |

推荐阅读