如何按SELECT子查询的最后一行排序？附posts与messages表结构

结合你给出的posts和messages表结构，要实现按每个帖子对应的消息子查询最后一行来排序帖子，咱们可以用两种简洁的SQL方案来实现：

方案一：窗口函数关联排序 #

这种方法先给每个帖子的消息按你定义的“最后一行”规则（比如message_id降序，代表最新的消息）编号，然后关联到帖子表，再按最后一条消息的字段排序：

SELECT p.*
FROM posts p
LEFT JOIN (
    -- 给每个post_id下的消息按message_id降序编号，rn=1就是最后一条
    SELECT post_id, message_id, message_text,
           ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY message_id DESC) AS rn
    FROM messages
) m ON p.post_id = m.post_id AND m.rn = 1
-- 按最后一条消息的message_id降序排序，也可以换成message_text等其他字段
ORDER BY m.message_id DESC;

如果你的“最后一行”不是按message_id，而是有其他规则（比如消息的创建时间，假设表中有created_at字段），只需要把ORDER BY message_id DESC换成ORDER BY created_at DESC即可。

方案二：ORDER BY中直接使用相关子查询 #

这种写法更简洁，直接在排序逻辑里嵌入子查询，获取每个帖子对应的最后一条消息字段：

SELECT p.*
FROM posts p
ORDER BY (
    -- 针对当前帖子，取它的最后一条消息的message_id
    SELECT m.message_id
    FROM messages m
    WHERE m.post_id = p.post_id
    ORDER BY m.message_id DESC
    LIMIT 1
) DESC;

处理无消息的帖子 #

如果有些帖子没有对应的消息记录，上面的子查询会返回NULL。不同数据库对NULL的排序规则略有差异（比如MySQL中NULL在降序排序时会排在最后），如果需要调整这类帖子的排序位置，可以用COALESCE给NULL设置默认值：

SELECT p.*
FROM posts p
ORDER BY COALESCE(
    (SELECT m.message_id
     FROM messages m
     WHERE m.post_id = p.post_id
     ORDER BY m.message_id DESC
     LIMIT 1),
    0 -- 给无消息的帖子设置默认排序值，这里设为0会让它们排在前面
) DESC;

内容的提问来源于stack exchange，提问作者user9644796