mysql - 使用联合表时如何选择不同的行

Question

我有两个名为 chatMaster 和 chatMessages 的表，我试图左连接这两个表以获取来自 master 的所有数据和每次聊天的最新消息。我正在使用这个查询来连接两个表

SELECT  c.id, c.p1, c.p2, m.toP, m.message, m.createdOn FROM chatMaster c
left join chatMessages m on c.id = m.id group by  m.id ;

此查询仅用于为 chatMaster 中的每个条目选择一行，但它为每个条目选择第一条消息，例如，

1, 2018-11-24 00:40:08, HI!,       99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1001
2, 2018-11-24 00:40:21, HI! There, 99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1
3, 2018-11-24 01:33:12, HI!,       e3345a17-ee7f-11e8-bc28-8acac6f59ef9, 2

这是 select * from chatMessages 的结果，聊天 ID 99e22056-ee7f-11e8-bc28-8acac6f59ef9 有两个条目，我想要发送一个在 2018-11-24 00:40:21 上发送，但我的连接查询的结果是

99e22056-ee7f-11e8-bc28-8acac6f59ef9, 1001,     1,    1001, HI!, 2018-11-24 00:40:08
e3345a17-ee7f-11e8-bc28-8acac6f59ef9,    2, 10001,       2, HI!, 2018-11-24 01:33:12

它正在选择第一条消息。如何选择最新消息？我应该做哪些更改才能从 chatMessages 获取最新消息而不是第一条消息。请询问您是否需要更多详细信息

Answer 1

在 Derived Table 中，您可以获得每个聊天管理员 id 的 createdOn(最新 createdOn)的最大值。然后，您可以将此结果集连接到主表，以仅获取与最新的 createdOn 对应的行。

SELECT  c.id, c.p1, c.p2, m.toP, m.message, m.createdOn 
FROM chatMaster c 
LEFT JOIN
( SELECT id, MAX(createdOn) AS latest_createdOn 
  FROM chatMessages 
  GROUP BY id
) AS dt 
  ON dt.id = c.id 
LEFT JOIN chatMessages m 
  ON m.id = c.id AND 
     m.createdOn = dt.latest_createdOn