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

编程入门 行业动态 更新时间:2024-10-22 08:43:26
本文介绍了MySQL:从彼此关联的 2 个表中获取最新消息的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述

首先,非常感谢您的帮助.

我有 2 个表:一个 conversation 表和一个 message 表,以及第三个表 assoc_message__conversation 将消息与对话相关联.

我需要获取为每个指定对话发送的最新 message_id 和 message,以及与之关联的 conversation_id.>

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

如您在此示例中所见,有两个 conversation_id 为 1 和 2 的对话,并且每个对话关联了三条消息.消息 1、2 和 3 与会话 1 相关联,消息 4、5 和 6 与会话 2 相关联.

我需要能够在 assoc_message__conversation 表(ID 1 和 2)中指定 conversation_id,并检索最新的 message_id、message 和关联的 conversation_id 从 message 表中为每个指定的对话发送.

所以它应该拉的行是:

conversation_id |message_id |信息------------------------------------------------1 |3 |最新消息"------------------------------------------------2 |6 |最新消息"------------------------------------------------

非常感谢您的帮助!

解决方案

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

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

架构(MySQL v5.7)

查看 DB Fiddle

查询 #1

SELECTamc.conversation_id,m.message_id,消息从assoc_message__conversation AS amc加入消息 AS mON m.message_id = amc.message_id加入(选择amc1.conversation_id,MAX(m1.send_datetime) AS latest_send_datetime从assoc_message__conversation AS amc1加入消息 AS m1ON m1.message_id = amc1.message_idWHERE amc1.conversation_id IN (1,2) -- 在这里提供输入过滤器GROUP BY amc1.conversation_id) 作为 dtON dt.conversation_id = amc.conversation_id ANDdt.latest_send_datetime = m.send_datetime;

结果

|对话_id |message_id |留言 ||--------------- |---------- |-------------- ||1 |3 |最新消息 ||2 |6 |最新消息 |

在 MySQL 8.0.2 及更高版本中,我们可以使用 Row_Number() 功能.在 conversation_id 的一个分区内,我们将确定每条消息的 Row Number,按照 send_datetime 的降序排序.在此子查询中,您可以在 WHERE 子句中为 session_id 提供过滤器.

然后我们将使用这个结果集作为派生表,并只考虑那些行号值为 1 的行(因为它将属于最新的 send_datetime).

架构(MySQL v8.0)

查看 DB Fiddle

查询 #2

SELECTdt.conversation_id,dt.message_id,dt.message从(选择amc.conversation_id,m.message_id,m. 消息,ROW_NUMBER() OVER(PARTITION BY amc.conversation_idORDER BY m.send_datetime DESC) AS row_no从assoc_message__conversation AS amc加入消息 AS mON m.message_id = amc.message_idWHERE amc.conversation_id IN (1,2) -- 在这里提供输入过滤器) 作为 dt哪里 dt.row_no = 1;

结果

|对话_id |message_id |留言 ||--------------- |---------- |-------------- ||1 |3 |最新消息 ||2 |6 |最新消息 |

First, thank you so much for your help.

I have 2 tables: a conversation table and a message table, and a third table assoc_message__conversation that associates the messages to a conversation.

I need to get the latest message_id and message sent for each conversation specified, along with the conversation_id it is associated with.

Here is a db-fiddle: www.db-fiddle/f/kxRQeGUYYgQ7FTwi96hbLp/0

As you can see in this example, there are two conversations with conversation_id of 1 and 2, and there are three messages associated to each conversation. Messages 1, 2, and 3 are associated to conversation 1, and messages 4, 5 and 6 are associated to conversation 2.

I need to be able to specify the conversation_id's in the assoc_message__conversation table (IDs 1 and 2), and retrieve the latest message_id, message and the associated conversation_id sent from the message table for each conversation specified.

So the rows it should pull are:

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

Thank you so much for your help!

解决方案

In older versions of MySQL (< 8.0.2), we can use Derived Tables. In a Derived table, we can get the latest send_datetime value for each conversation_id. Also, it is noteworthy that you can provide your filters for conversation_id in the WHERE clause of this subquery.

We can then use this subquery`s result-set and join back to the main tables appropriately, to get the row corresponding to the latest message in a conversation.

Schema (MySQL v5.7)

View on DB Fiddle

Query #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;

Result

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

In MySQL 8.0.2 and above, we can use Row_Number() functionality. Within a partition of conversation_id, we will determine Row Number for every message, sorted in descending order of send_datetime. In this subquery, you can provide your filters for conversation_id in the WHERE clause.

We will then use this result-set as a Derived Table, and consider only those rows, where Row Number value is 1 (as it will belong to latest send_datetime).

Schema (MySQL v8.0)

View on DB Fiddle

Query #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;

Result

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

更多推荐

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

本文发布于:2023-10-24 14:36:18,感谢您对本站的认可!
本文链接:https://www.elefans.com/category/jswz/34/1524196.html
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。
本文标签:最新消息   MySQL   个表中

发布评论

评论列表 (有 0 条评论)
草根站长

>www.elefans.com

编程频道|电子爱好者 - 技术资讯及电子产品介绍!