sql - 根据相关的聚合列选择分组依据中的非聚合列

我正试图从数据库中获取对话列表，我希望将最近的消息与它们一起显示。我很难找到一个允许我做这件事的查询（而且它不是N+1）。
我有这些桌子：

chats(id, user_id, post_id, created_at)
messages(id, user_id, chat_id, body, created_at)

我有一个这样的疑问：

select chats.id, chats.user_id, m.latest from chats
inner join (
    select chat_id, max(created_at) as latest from messages
    group by chat_id
) as m on m.chat_id = chats.id;

但我想从max(created_at)结果对应的行中添加消息体。有可能得到这样一个相关的专栏吗？

最佳答案

使用标准SQL，这可以使用窗口函数来完成（参见sagi的答案）。
对于Postgres，专有的distinct on()通常比使用窗口函数的等效解决方案快：

select chats.id, chats.user_id, m.body, m.latest
from chats
  join (
    select distinct on (chat_id) chat_id, body, created_at as latest
    from messages
    order by chat_id, created_at desc;
  ) as m on m.chat_id = chats.id;

使用窗口函数的解决方案更灵活。您还可以用它获得最近的两条（或三条或…）消息，而distinct on()只能得到一条。