MySQL SQL – 获取每个对话的最后一条消息

在本文中，我们将介绍如何使用MySQL SQL查询每个对话的最后一条消息。通过这种方法，我们可以更有效地管理我们的消息记录，并跟踪我们的对话历史。

阅读更多：MySQL 教程

创建示例表格和数据

我们将首先创建一个示例表格来模拟我们的消息记录。表格的结构如下：

CREATE TABLE messages (
  message_id INT NOT NULL AUTO_INCREMENT,
  conversation_id INT NOT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  PRIMARY KEY (message_id)
);

我们还将插入一些示例数据来填充我们的表格。要插入一条消息记录，我们需要指定对话ID、消息文本和创建时间。例如：

INSERT INTO messages (conversation_id, message, created_at) VALUES (1, '你在哪里？', '2021-11-01 08:00:00');
INSERT INTO messages (conversation_id, message, created_at) VALUES (1, '我在家里。你呢？', '2021-11-01 08:02:00');
INSERT INTO messages (conversation_id, message, created_at) VALUES (2, '你吃早餐了吗？', '2021-11-01 08:05:00');
INSERT INTO messages (conversation_id, message, created_at) VALUES (2, '我没有，你呢？', '2021-11-01 08:07:00');
INSERT INTO messages (conversation_id, message, created_at) VALUES (1, '我现在在学校。你要来吗？', '2021-11-01 08:10:00');

使用子查询获取每个对话的最后一条消息

要获取每个对话的最后一条消息，我们可以使用一个子查询和MAX函数。子查询将从消息表中选择每个对话的创建时间的最大值，然后主查询将返回与这些最大值匹配的消息记录。

SELECT m1.*
FROM messages m1
INNER JOIN (
  SELECT conversation_id, MAX(created_at) AS max_created_at
  FROM messages
  GROUP BY conversation_id
) m2 ON m1.conversation_id = m2.conversation_id AND m1.created_at = m2.max_created_at;

这个查询将返回以下结果：

+------------+-----------------+--------------------------------------+---------------------+
| message_id | conversation_id | message                              | created_at          |
+------------+-----------------+--------------------------------------+---------------------+
| 2          | 1               | 我在家里。你呢？                    | 2021-11-01 08:02:00 |
| 4          | 2               | 我没有，你呢？                      | 2021-11-01 08:07:00 |
| 5          | 1               | 我现在在学校。你要来吗？            | 2021-11-01 08:10:00 |
+------------+-----------------+--------------------------------------+---------------------+