基于PostgreSQL实现帖子、评论及回复的单SQL查询与聚合统计

数据库表结构及数据 #

posts表 #

comments表 #

replies表 #

需求说明 #

需求1：单SQL获取帖子+前2条评论+每条评论前2条回复 #

给定postid（例如postid=1），通过单次数据库调用获取以下内容并返回嵌套结构：

• 帖子内容
• 该帖子按created_at排序的前2条评论
• 每条上述评论按created_at排序的前2条回复

期望返回数据结构示例：

const POST = {
    postid: 1,
    body: "...",
    comments: [{
        commentid: 78,
        body: "耶耶耶",
        replies: [{replyid: 167, body: "耶耶耶耶"}, ...]
    }, ...]
}

需求2：添加聚合统计字段 #

在需求1的SQL查询基础上，增加以下聚合统计：

• 该帖子的评论总数
• 每条评论的回复总数

期望返回数据结构示例：

const POST = {
    postid: 1,
    body: "...",
    comments: [{
        commentid: 78,
        body: "耶耶耶",
        replies: [...],
        reply_aggregate: 1
    }, ...],
    comments_aggregate: 2
}

解决方案 #

针对需求1的SQL语句（PostgreSQL版本） #

利用ROW_NUMBER()窗口函数筛选前N条数据，结合JSON_AGG()生成嵌套结构：

SELECT
    p.postid,
    p.body,
    JSON_AGG(
        JSON_BUILD_OBJECT(
            'commentid', c.commentid,
            'body', c.body,
            'created_at', c.created_at,
            'replies', (
                SELECT JSON_AGG(
                    JSON_BUILD_OBJECT(
                        'replyid', r.replyid,
                        'body', r.body,
                        'created_at', r.created_at
                    ) ORDER BY r.created_at
                )
                FROM (
                    SELECT *
                    FROM replies
                    WHERE commentid = c.commentid
                    ORDER BY created_at
                    LIMIT 2
                ) r
            )
        ) ORDER BY c.created_at
    ) AS comments
FROM posts p
LEFT JOIN (
    SELECT *
    FROM comments
    WHERE postid = 1 -- 替换为目标postid
    ORDER BY created_at
    LIMIT 2
) c ON p.postid = c.postid
WHERE p.postid = 1 -- 替换为目标postid
GROUP BY p.postid, p.body;

针对需求2的SQL语句（PostgreSQL版本） #

在需求1的基础上，增加聚合统计子查询：

SELECT
    p.postid,
    p.body,
    -- 帖子的评论总数
    (SELECT COUNT(*) FROM comments WHERE postid = p.postid) AS comments_aggregate,
    JSON_AGG(
        JSON_BUILD_OBJECT(
            'commentid', c.commentid,
            'body', c.body,
            'created_at', c.created_at,
            -- 单条评论的回复总数
            'reply_aggregate', (SELECT COUNT(*) FROM replies WHERE commentid = c.commentid),
            'replies', (
                SELECT JSON_AGG(
                    JSON_BUILD_OBJECT(
                        'replyid', r.replyid,
                        'body', r.body,
                        'created_at', r.created_at
                    ) ORDER BY r.created_at
                )
                FROM (
                    SELECT *
                    FROM replies
                    WHERE commentid = c.commentid
                    ORDER BY created_at
                    LIMIT 2
                ) r
            )
        ) ORDER BY c.created_at
    ) AS comments
FROM posts p
LEFT JOIN (
    SELECT *
    FROM comments
    WHERE postid = 1 -- 替换为目标postid
    ORDER BY created_at
    LIMIT 2
) c ON p.postid = c.postid
WHERE p.postid = 1 -- 替换为目标postid
GROUP BY p.postid, p.body;

适配其他数据库说明 #

• MySQL：可使用JSON_ARRAYAGG()和JSON_OBJECT()替代PostgreSQL的JSON函数
• SQL Server：可使用FOR JSON PATH语法生成嵌套JSON结构
• 可将postid = 1替换为查询参数，实现动态查询

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