MySQL新手求助：如何按点赞与评论数获取各社区的Top2帖子

Hey there! Let's figure out why your current query isn't working and get you the right SQL to fetch the top 2 posts per community sorted by likes and comments.

你的原查询问题分析 #

首先，咱们看看你写的SQL哪里出问题了：

SELECT * FROM stories JOIN stories_total_comments stc ON stories.story_id = stc.story_id JOIN stories_likes_counter slc ON stories.story_id = slc.story_id GROUP BY stories.community_id ORDER BY total_likes AND total_comments;

• GROUP BY的误用：当你用GROUP BY community_id时，MySQL默认只会返回每个社区的第一行数据（如果关闭了ONLY_FULL_GROUP_BY模式），这完全不是你要的"每个社区Top2"的结果。而且SELECT *搭配GROUP BY是不规范的，非聚合列和分组列无关时，结果是不确定的。
• ORDER BY的错误：ORDER BY total_likes AND total_comments是逻辑判断（返回0或1），不是按两个字段排序的正确写法，正确的应该是ORDER BY total_likes DESC, total_comments DESC（先按点赞降序，点赞相同则按评论降序）。

正确的解决方案：使用窗口函数 #

MySQL 8.0及以上版本支持窗口函数，这是实现"分组取TopN"最简洁的方式。我们可以用ROW_NUMBER()函数给每个社区内的帖子按点赞+评论数排序编号，然后筛选出编号≤2的帖子。

完整SQL语句

WITH ranked_stories AS (
    SELECT 
        s.*,
        stc.total_comments,
        slc.total_likes,
        ROW_NUMBER() OVER (
            PARTITION BY s.community_id 
            ORDER BY slc.total_likes DESC, stc.total_comments DESC
        ) AS rn
    FROM stories s
    JOIN stories_total_comments stc ON s.story_id = stc.story_id
    JOIN stories_likes_counter slc ON s.story_id = slc.story_id
    WHERE s.is_deleted = 0 -- 可选：过滤已删除的帖子
)
SELECT * FROM ranked_stories WHERE rn <= 2;

代码解释

1. CTE（公共表表达式）ranked_stories：
   • 关联stories、stories_total_comments和stories_likes_counter三张表，获取每个帖子的完整信息、评论数和点赞数。
   • ROW_NUMBER() OVER (PARTITION BY s.community_id ORDER BY slc.total_likes DESC, stc.total_comments DESC) AS rn：
     • PARTITION BY s.community_id：按社区ID分组，把同一社区的帖子放在一组。
     • ORDER BY slc.total_likes DESC, stc.total_comments DESC：每组内按点赞数降序排序，点赞数相同则按评论数降序排序。
     • rn是每个帖子在所属社区内的排名编号，每个社区的前2个帖子编号为1和2。
2. 最终筛选：从ranked_stories中选出rn <= 2的记录，就是每个社区的Top2帖子。

关于并列情况的补充说明 #

如果你希望点赞+评论数相同的帖子都被保留（比如某社区有3个帖子点赞和评论数都一样，都算Top2），可以把ROW_NUMBER()换成RANK()或者DENSE_RANK()：

• RANK()：相同排名会跳过后续编号（比如1,1,3）
• DENSE_RANK()：相同排名不会跳过编号（比如1,1,2）

根据你的需求选择合适的函数即可。

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