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

Question

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

我有 2 个表:一个 conversation table 和一个 message表和第三张表 assoc_message__conversation将消息与对话相关联。

我需要获取最新的 message_id和 message为指定的每个对话发送，连同 conversation_id它与相关联。

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

正如您在此示例中所见，有两个与 conversation_id 的对话1 和 2，每个对话关联三个消息。消息 1、2 和 3 与 session 1 关联，消息 4、5 和 6 与 session 2 关联。

我需要能够指定 conversation_id在 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"
------------------------------------------------

非常感谢您的帮助!

Answer 1

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

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

模式(MySQL v5.7)

View on 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 的行(因为它将属于最新的 send_datetime)。

架构(MySQL v8.0)

View on 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 |