如何为指定post_id列表中的每个ID获取前3条有效评论？

原查询中的LIMIT 3是全局限制，只会返回整个结果集的前3条记录，无法实现每个post_id单独取前3条评论。要实现这个需求，需要使用窗口函数对每个分组（post_id）内的记录进行排序和筛选。

具体SQL实现 #

SELECT id, body, created_at, like_count, post_id
FROM (
    SELECT 
        comments.id, 
        comments.body, 
        comments.created_at,
        comments.like_count, 
        comments.post_id,
        -- 按post_id分组，每组内按评论ID排序并生成行号
        ROW_NUMBER() OVER(PARTITION BY comments.post_id ORDER BY comments.id) AS rn
    FROM comments
    WHERE comments.is_active AND comments.is_show 
      AND comments.post_id IN (1, 7, 9, 11, 3)
) AS ranked_comments
WHERE rn <= 3
-- 可选：按帖子ID和行号排序，让结果更规整
ORDER BY post_id, rn;

关键部分说明 #

• ROW_NUMBER() OVER(PARTITION BY comments.post_id ORDER BY comments.id)：
  • PARTITION BY comments.post_id：将数据按帖子ID拆分为独立分组
  • ORDER BY comments.id：定义每个分组内评论的排序规则（如果需要按最新评论排序，可改为ORDER BY created_at DESC；按点赞数排序则用ORDER BY like_count DESC）
  • 生成的rn字段是每个分组内的行号，从1开始递增
• 外层查询通过WHERE rn <=3筛选出每个分组的前3条记录
• 原查询中的GROUP BY comments.id可以移除，因为窗口函数已经完成分组逻辑，且comments.id作为主键，分组后不会改变结果

可选调整：处理并列排序 #

如果需要允许同一条帖子内有相同排序值的评论（比如多条评论同时是点赞最高的），可以用RANK()或DENSE_RANK()替代ROW_NUMBER()：

• RANK()：相同排序值会获得相同行号，后续行号会跳过（比如1,1,3）
• DENSE_RANK()：相同排序值获得相同行号，后续行号连续（比如1,1,2）

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