PostgreSQL递归查询：统计指定节点的全量评论总数

我有一个网站，用户可在节点上发表评论，也可对其他评论进行无层级限制的回复。评论表的简化结构如下，entity_type和entity_id联合标识评论所属的实体：

评论表结构 #

数据的层级结构如下：

node with id 1
 |- (cid: 1) initial comment on node with id 1
 |   |- (cid: 2) reply to comment with id 1
 |   |   |- (cid: 3) reply to comment with id 2
 |   |- (cid: 4) second reply on first comment
 |- (cid: 6) second direct child comment on node 1
node with id 2
 |- (cid: 5) comment on another node
node with id 3
 |- (cid: 7) comment on a third node

需要编写SQL查询，返回指定节点ID及其对应的所有评论总数（包含所有层级的回复）。例如查询节点1和2时，期望结果：

尝试过多个WITH RECURSIVE递归查询但未得到正确结果，寻求帮助。

使用递归CTE（公共表表达式）可以遍历所有层级的评论，关联到对应的根节点，最后统计总数。以下是适配MySQL、PostgreSQL等支持WITH RECURSIVE的数据库的查询语句：

WITH RECURSIVE comment_hierarchy AS (
    -- 基础查询：获取所有直接关联到目标节点的评论，记录对应的根node_id
    SELECT
        c.cid,
        c.entity_id AS node_id
    FROM comments c
    WHERE c.entity_type = 'node'
      AND c.entity_id IN (1, 2) -- 指定要查询的节点ID
    UNION ALL
    -- 递归查询：获取所有回复，继承父评论对应的根node_id
    SELECT
        child.cid,
        parent.node_id
    FROM comments child
    JOIN comment_hierarchy parent ON child.entity_type = 'comment' AND child.entity_id = parent.cid
)
-- 统计每个节点对应的所有评论数量
SELECT
    node_id AS node,
    COUNT(cid) AS "# comments"
FROM comment_hierarchy
GROUP BY node_id
ORDER BY node_id;

代码解释 #

1. 基础查询部分：筛选出所有直接属于目标节点的评论（entity_type='node'），并将entity_id作为该评论对应的根节点ID。
2. 递归查询部分：通过关联子评论的entity_id（父评论的cid），将子评论关联到同一个根节点ID，实现层级遍历。
3. 统计部分：对递归得到的所有评论按根节点分组，统计每个节点的评论总数。

验证结果 #

执行上述查询后，会得到期望的结果：

如果需要动态指定节点ID，可以将IN (1,2)替换为对应数据库的参数语法（比如MySQL用?，PostgreSQL用$1等）。

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