如何编写MySQL查询获取按创建日期和最新回复日期排序的更新帖子列表

核心逻辑 #

要实现符合社交平台逻辑的更新帖子列表，只需要为每个帖子计算最后活动时间：

• 有回复的帖子取最新一条回复的发布时间
• 没有回复的帖子取帖子本身的创建时间
  最终按最后活动时间倒序排列即可。

通用实现写法（兼容大多数数据库） #

SELECT 
  p.id,
  p.title,
  p.added_on AS post_create_time,
  MAX(pr.added_on) AS latest_reply_time,
  -- 无回复时用帖子创建时间作为活动时间
  COALESCE(MAX(pr.added_on), p.added_on) AS last_activity_time
FROM posts p
LEFT JOIN post_replies pr ON p.id = pr.post_id
GROUP BY p.id, p.title, p.added_on
ORDER BY last_activity_time DESC

语句说明 #

• 使用LEFT JOIN关联两张表，保证没有回复的新帖子也能正常出现在结果中
• 通过MAX(pr.added_on)聚合得到每个帖子的最新回复时间
• COALESCE是标准SQL函数，作用是取第一个非空值，完美处理无回复帖子的活动时间赋值

需要展示最新回复内容的写法（支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL等） #

你之前写的回复查询语句不符合SQL标准，分组后直接取id、comment字段无法保证拿到的是最新回复的内容，用窗口函数可以解决这个问题：

WITH latest_reply AS (
  SELECT
    post_id,
    comment AS latest_reply_content,
    added_on AS reply_time,
    -- 同一帖子的回复按时间倒序编号，取编号为1的即为最新回复
    ROW_NUMBER() OVER(PARTITION BY post_id ORDER BY added_on DESC) AS rn
  FROM post_replies
)
SELECT
  p.id,
  p.title,
  p.added_on AS post_create_time,
  lr.latest_reply_content,
  lr.reply_time AS latest_reply_time,
  COALESCE(lr.reply_time, p.added_on) AS last_activity_time
FROM posts p
LEFT JOIN latest_reply lr ON p.id = lr.post_id AND lr.rn = 1
ORDER BY last_activity_time DESC

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