PostgreSQL如何查询评论最多、最少的帖子及对应发布用户信息

需求说明 #

你需要的结果包含三个核心信息：帖子评论数、帖子基础信息、帖子发布用户信息，我们分步骤实现：

1 基础统计：所有帖子的评论数+发布用户信息 #

先获取全量帖子的统计结果，这是后续筛选最多/最少的基础：

SET search_path TO perf_demo; -- 切换到你的测试 schema

SELECT
    p.id AS post_id,
    p.title AS post_title,
    u.id AS publisher_user_id,
    u.email AS publisher_email,
    COUNT(c.id) AS comment_count -- 统计每个帖子的评论数
FROM posts p
-- 关联发布用户表，拿到发帖人信息
INNER JOIN users u ON p.user_id = u.id
-- 左连接评论表，保证0评论的帖子也会出现在结果里
LEFT JOIN comments c ON p.id = c.post_id
GROUP BY p.id, u.id -- 按帖子、用户分组
ORDER BY comment_count DESC;

注意：这里用LEFT JOIN而不是INNER JOIN关联评论表，是为了避免漏掉没有任何评论的帖子，如果不需要0评论的可以换成INNER JOIN。

2 查询评论数最多的帖子（支持并列第一） #

如果直接用LIMIT 1只会返回一条结果，有多个帖子评论数相同且都是最多时会被漏掉，用窗口函数RANK()可以解决这个问题：

SET search_path TO perf_demo;

WITH post_comment_stats AS (
    SELECT
        p.id AS post_id,
        p.title AS post_title,
        u.id AS publisher_user_id,
        u.email AS publisher_email,
        COUNT(c.id) AS comment_count,
        -- 按评论数倒序排名，相同评论数排名相同
        RANK() OVER (ORDER BY COUNT(c.id) DESC) AS comment_count_rank
    FROM posts p
    INNER JOIN users u ON p.user_id = u.id
    LEFT JOIN comments c ON p.id = c.post_id
    GROUP BY p.id, u.id
)
SELECT * FROM post_comment_stats WHERE comment_count_rank = 1;

如果不需要保留并列结果，直接在基础统计SQL末尾加LIMIT 1即可。

3 查询评论数最少的帖子（支持并列倒数第一） #

逻辑和查最多的完全一致，仅调整窗口函数的排序规则为升序即可：

SET search_path TO perf_demo;

WITH post_comment_stats AS (
    SELECT
        p.id AS post_id,
        p.title AS post_title,
        u.id AS publisher_user_id,
        u.email AS publisher_email,
        COUNT(c.id) AS comment_count,
        -- 按评论数升序排名，相同评论数排名相同
        RANK() OVER (ORDER BY COUNT(c.id) ASC) AS comment_count_rank
    FROM posts p
    INNER JOIN users u ON p.user_id = u.id
    LEFT JOIN comments c ON p.id = c.post_id
    GROUP BY p.id, u.id
)
SELECT * FROM post_comment_stats WHERE comment_count_rank = 1;

4 一次查询同时返回最多和最少评论的帖子 #

如果希望一次拿到两种结果，可以用如下写法：

SET search_path TO perf_demo;

WITH post_comment_stats AS (
    SELECT
        p.id AS post_id,
        p.title AS post_title,
        u.id AS publisher_user_id,
        u.email AS publisher_email,
        COUNT(c.id) AS comment_count,
        RANK() OVER (ORDER BY COUNT(c.id) DESC) AS rank_desc,
        RANK() OVER (ORDER BY COUNT(c.id) ASC) AS rank_asc
    FROM posts p
    INNER JOIN users u ON p.user_id = u.id
    LEFT JOIN comments c ON p.id = c.post_id
    GROUP BY p.id, u.id
)
SELECT 
    *,
    CASE 
        WHEN rank_desc = 1 THEN '评论数最多'
        WHEN rank_asc = 1 THEN '评论数最少'
    END AS result_type
FROM post_comment_stats 
WHERE rank_desc = 1 OR rank_asc = 1;

实操示例（基于你提供的测试数据） #

你提供的测试数据默认生成5个用户、8个帖子、10条评论，执行上述SQL后输出示例如下：

新手注意事项 #

• 如果你只需要固定数量的结果（比如前3多/前3少），可以把WHERE条件里的=1改成<=3
• 如果数据量超过10万条，建议给comments.post_id字段加索引，查询速度会提升很多：CREATE INDEX idx_comments_post_id ON perf_demo.comments(post_id);
• 如果你不需要统计0评论的帖子，把LEFT JOIN comments改成INNER JOIN comments即可

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