mysql - 这可以在一个快速的 mysql 查询中实现吗？

Question

我有三个表，我需要所有表中的不同数据。遗憾的是我还需要能够提取最新行。

这是我的表格:

消息:我只是将消息内容存储在表格中，因为一个文本可以发送给多个用户

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| message_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| content    | varchar(255)     | NO   |     | 0       |                |
+------------+------------------+------+-----+---------+----------------+

对话:此表仅反射(reflect)两个用户之间的单个对话。

+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| partner_id      | int(10) unsigned | NO   | MUL | NULL    |                |
| conversation_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| expedition_id   | int(11)          | NO   |     | NULL    |                |
| active          | tinyint(4)       | NO   |     | 1       |                |
+-----------------+------------------+------+-----+---------+----------------+

conversation_messages:该表存储有关实际交换消息的信息。

+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| message_id      | int(11) unsigned | NO   | PRI | NULL    |       |
| receiver_id     | int(11) unsigned | NO   | PRI | NULL    |       |
| conversation_id | int(11) unsigned | NO   | MUL | NULL    |       |
| status          | tinyint(4)       | NO   |     | NULL    |       |
| timestamp       | datetime         | YES  |     | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+

我现在想要做的是选择每个对话中的最新消息并从中获取内容。 (听起来很简单，但并没有找到简单的解决办法)。我尝试的是以下内容:

SELECT max(c_m.message_id), m.content, c_m.`status` 
FROM expedition_conversations e_c, conversation_messages c_m 
    INNER JOIN messages m ON m.message_id = c_m.message_id 
WHERE e_c.expedition_id = 1 AND (c_m.conversation_id = e_c.conversation_id)      
GROUP BY c_m.conversation_id;

遗憾的是，由于GROUP BY内部似乎大多数时候都选择第一个插入的行，所以我从messages表中选择的content是错误，而从 conversation_messages 中选择的 message_id 是正确的。

知道如何在一个查询中执行此操作吗？如果您有任何更改表结构的建议，我也将不胜感激。

提前致谢。

Answer 1

您可能想尝试此版本:

SELECT c_m.message_id, m.content, c_m.`status` 
FROM expedition_conversations e_c join
     conversation_messages c_m 
     ON c_m.conversation_id = e_c.conversation_id INNER JOIN
     messages m
     ON m.message_id = c_m.message_id 
WHERE e_c.expedition_id = 1 AND
      NOT EXISTS (SELECT 1
                  FROM conversation_messages cm2
                  WHERE cm2.conversation_id = c_m.conversation_id AND
                        cm2.timestamp > c_m.timestamp
                 ) 

为了提高性能，您需要在 conversation_messages(conversation_id, timestamp) 上建立索引。