MySQL：在LEFT JOIN前用变量值限制子查询，实现单帖评论条数限制

没问题，我来帮你实现按帖子限制评论数量的查询！

假设你想给每个主帖（parentid = 0的记录）最多返回指定数量的评论，我们可以通过用户变量给每个主帖下的评论编号，再筛选出符合数量限制的评论，最后和主帖表关联。以下是完整的查询语句：

SET @current_post := 0, @comment_count := 0;

SELECT 
    main_posts.id AS post_id,
    main_posts.content AS post_content,
    main_posts.userid AS post_userid,
    limited_comments.id AS comment_id,
    limited_comments.content AS comment_content,
    limited_comments.userid AS comment_userid
FROM 
    posts main_posts
LEFT JOIN (
    SELECT 
        id,
        content,
        parentid,
        userid,
        @comment_count := CASE 
            WHEN @current_post = parentid THEN @comment_count + 1 
            ELSE 1 
        END AS comment_rank,
        @current_post := parentid AS dummy
    FROM 
        posts
    WHERE 
        parentid != 0  -- 仅处理评论数据
    ORDER BY 
        parentid, id  -- 按主帖ID排序，再按评论ID排序（可改为时间等其他规则）
) limited_comments ON main_posts.id = limited_comments.parentid
WHERE 
    limited_comments.comment_rank <= 3  -- 这里设置每个帖子最多显示的评论数，比如3条
    OR limited_comments.comment_rank IS NULL  -- 保留没有评论的主帖
ORDER BY 
    main_posts.id, limited_comments.comment_rank;

代码细节解释： #

• 变量初始化：SET @current_post := 0, @comment_count := 0; 初始化两个变量，@current_post跟踪当前处理的主帖ID，@comment_count给当前主帖下的评论按顺序编号。
• 子查询生成带排名的评论：遍历所有评论（parentid != 0），通过CASE语句判断当前评论所属主帖是否和上一条一致：
  • 若一致，评论编号+1；
  • 若不一致，重置编号为1，同时更新@current_post为当前主帖ID。
• 关联与筛选：用LEFT JOIN关联主帖表和带排名的评论子查询，筛选出排名≤限制数的评论，同时保留无评论的主帖（OR limited_comments.comment_rank IS NULL）。
• 自定义调整：
  • 修改评论数量限制：把WHERE子句里的3改成你需要的数字即可；
  • 调整评论排序规则：修改子查询的ORDER BY，比如改成parentid, id DESC就能让最新评论排在前面。

用你的示例数据测试的话，主帖ID为1的评论有3条（ID3、4、6），如果限制3条就会全部显示；如果限制2条，就只会显示前两条（按你设置的排序规则）。

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