SQL - 如何选择所有聊天并按最新消息排序

Question

我正在创建一个简单的聊天应用程序，我想选择所有 Chat 并按它们最近的 Message 对它们进行排序(列 created_on ) 。2个表是分开的，说明如下:

聊天

消息

为此，我编写了以下 SQL 脚本:

SELECT * CHAT AS chat LEFT JOIN MESSAGE AS message ON chat.ID = message.CHAT_ID
GROUP BY chat.ID
ORDER BY message.CREATED_ON

当我运行该脚本时，结果不是按最新 Message 排序，而是随机排序。而且我还注意到脚本并不快。

Answer 1

您的查询格式不正确，因为您将 SELECT * 与 GROUP BY 一起使用。 (这会忽略其他语法错误，例如缺少 FROM 子句。)您的查询应该返回错误。

您似乎不仅希望对聊天进行排序，还希望包括最新的消息。为此，使用窗口函数:

SELECT c.*, m.*
FROM chat c LEFT JOIN
     (SELECT m.*,
             ROW_NUMBER() OVER (PARTITION BY m.chat_id ORDER BY m.CREATED_ON DESC) as seqnum
      FROM MESSAGE m
     ) m
     ON c.ID = m.CHAT_ID AND m.seqnum = 1
ORDER BY m.CREATED_ON DESC;

在大多数数据库中，message(chat_id, created_on) 上的索引会有所帮助。