如何使用递归SQL实现分类分组及后代条目统计？

表结构 #

数据库包含三张表：

• Category：id（主键）、name、parent_crawler_id、crawler_id
• Item：id（主键）、name
• ItemCategories：id、item_id（外键关联Item.id）、category_id（外键关联Category.id）

示例数据 #

Category表 #

id (pk)      name              parent_crawler_id            crawler_id
1            Fruit and Veg     null                         8899
2            Organic           8899                         88100
3            Green             88100                        88101

Item表 #

id(pk)       name              
1            Grapes
2            Apples

ItemCategories表 #

id           item_id (fk)      category_id (fk)
1            1                 2
2            2                 3
3            1                 3
4            2                 2

需求 #

统计每个分类及其所有后代分类关联的条目总数，期望输出：

name            count
Fruit and Veg   6
Organic         4
Green           2

原查询问题 #

你编写的递归CTE仅遍历出了所有分类节点，但未建立每个祖先分类与所有后代分类（含自身）的关联关系，导致最终仅统计了当前分类的条目数，未累加后代分类的关联数量。

正确的递归SQL实现 #

WITH RECURSIVE category_hierarchy AS (
    -- 基础部分：每个分类作为自身的祖先
    SELECT 
        id AS ancestor_id, 
        name AS ancestor_name,
        id AS descendant_id
    FROM "Category"
    
    UNION ALL
    
    -- 递归部分：关联父分类的祖先与子分类
    SELECT 
        ch.ancestor_id, 
        ch.ancestor_name,
        c.id AS descendant_id
    FROM "Category" c
    JOIN category_hierarchy ch ON c.parent_crawler_id = (SELECT crawler_id FROM "Category" WHERE id = ch.descendant_id)
)
-- 统计每个祖先分类对应的所有后代分类的条目总数
SELECT 
    ch.ancestor_name AS name,
    COUNT(ic.item_id) AS count
FROM category_hierarchy ch
LEFT JOIN "ItemCategories" ic ON ic.category_id = ch.descendant_id
GROUP BY ch.ancestor_id, ch.ancestor_name
ORDER BY ch.ancestor_name;

逻辑说明 #

1. 递归CTE category_hierarchy：生成每个分类（祖先）与其所有后代分类（含自身）的关联关系，比如Fruit and Veg会关联自身、Organic、Green三个分类的ID。
2. 关联统计：将生成的层级关系与ItemCategories关联，统计所有后代分类对应的条目数总和，最后按祖先分类分组得到结果。

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