PostgreSQL层级分类结构中子分类数量统计方法咨询

嘿，我来帮你搞定这个分类表的子分类统计问题！根据你的需求，我分两种常见场景给你解决方案：

一、统计每个分类的所有层级子分类总数（含孙子/曾孙等） #

如果你需要统计每个分类下面所有层级的子分类数量（比如一级分类下的二级、三级都算进去），可以在你现有的递归查询基础上扩展，通过追踪每个节点的所有祖先来实现：

WITH RECURSIVE category_tree AS (
    -- 锚点：先把所有分类作为初始节点，每个节点的初始祖先就是自己
    SELECT 
        id AS node_id, 
        name AS node_name, 
        parent_id,
        id AS ancestor_id,
        name AS ancestor_name
    FROM category
    UNION ALL
    -- 递归：把当前节点的父节点关联进来，让父节点的祖先也成为当前节点的祖先
    SELECT 
        ct.node_id, 
        ct.node_name, 
        ct.parent_id,
        c.id AS ancestor_id,
        c.name AS ancestor_name
    FROM category_tree ct
    JOIN category c ON ct.parent_id = c.id
)
-- 分组统计每个分类的子分类总数，减去1是排除自身
SELECT 
    ancestor_id AS category_id,
    ancestor_name AS category_name,
    COUNT(DISTINCT node_id) - 1 AS total_subcategory_count
FROM category_tree
GROUP BY ancestor_id, ancestor_name
ORDER BY category_id;

代码解释： #

• 递归CTE category_tree 会为每个节点记录它的所有祖先节点（包括自己）。比如一个三级分类，它的祖先列表会包含它自己、二级分类、一级分类。
• 最后分组时，按祖先分类的id和name分组，统计该祖先下所有不同的node_id数量，再减去1（因为每个祖先自己也会被计入node_id，我们要的是它的后代数量），就得到了该分类所有层级的子分类总数。

二、统计每个分类的直接子分类数量（仅下一级） #

如果只需要统计每个分类的直接子节点（比如一级分类只算二级，不算三级），那就简单多了，不需要递归，直接分组计数即可：

SELECT 
    parent_id AS category_id,
    c.name AS category_name,
    COUNT(*) AS direct_subcategory_count
FROM category
LEFT JOIN category c ON category.parent_id = c.id
WHERE parent_id IS NOT NULL  -- 过滤掉没有父节点的根分类（如果你的表有这类数据）
GROUP BY parent_id, c.name
ORDER BY category_id;

代码解释： #

• 直接按parent_id分组，统计每个parent_id对应的记录数，就是该分类的直接子分类数量。
• 用LEFT JOIN是为了确保能关联到父分类的名称，如果你只需要分类ID的话，可以去掉这个关联，直接按parent_id分组计数。

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