基于CTE递归与特定层级GROUP BY的文件夹结构数据统计需求

没问题，我来帮你搞定这个递归文件夹的统计需求！针对你这种用id和parent字段维护的层级结构，我们可以用CTE递归轻松计算层级，再按第3层分组统计数据。

首先，假设你的核心表结构是这样的（可根据实际字段名调整）：

• folder_items：存储文件夹与项目关联关系的表
• id：当前记录的唯一标识
• parent_id：父文件夹的ID
• item_id：关联项目表的外键（你提到的item字段示例）

步骤1：递归CTE计算层级和关联的3层节点 #

我们先通过递归CTE给每条记录计算层级depth，同时标记出它所属的第3层父节点top3_id——这个字段是关键，能让所有深层记录都关联到对应的第3层节点，方便后续分组统计。

WITH recursive_folder AS (
    -- 锚点成员：顶层文件夹（这里假设顶层的parent_id为NULL，根据你的数据可调整为0或其他值）
    SELECT 
        id,
        parent_id,
        item_id,
        1 AS depth,
        id AS top3_id
    FROM folder_items
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归成员：关联父节点，计算当前层级和top3_id
    SELECT 
        child.id,
        child.parent_id,
        child.item_id,
        parent.depth + 1 AS depth,
        -- 逻辑：父节点没到第3层时，继承父的top3_id；父节点是第3层时，用父ID作为当前记录的top3_id
        CASE 
            WHEN parent.depth < 3 THEN parent.top3_id
            ELSE parent.id
        END AS top3_id
    FROM folder_items child
    JOIN recursive_folder parent ON child.parent_id = parent.id
)

步骤2：筛选深层记录并按3层节点统计 #

接下来，我们筛选出深度超过3层的记录，然后按top3_id分组，统计每个第3层节点下的项目数量。如果需要包含第3层本身的项目，把WHERE rf.depth > 3改成WHERE rf.depth >= 3即可。

SELECT 
    top3.id AS level3_folder_id,
    -- 如果你的文件夹表有名称字段，可以加上top3.folder_name让结果更直观
    COUNT(rf.item_id) AS total_items_under_level3
FROM recursive_folder rf
-- 关联回文件夹表，获取第3层节点的完整信息
JOIN folder_items top3 ON rf.top3_id = top3.id
WHERE rf.depth > 3  -- 只统计深度超过3层的项目
GROUP BY top3.id
ORDER BY total_items_under_level3 DESC;

针对关联多项目表的调整 #

如果item_id是关联到其他项目表的外键，需要统计实际项目的去重数量，可以修改查询如下：

SELECT 
    top3.id AS level3_folder_id,
    COUNT(DISTINCT p.id) AS total_project_count
FROM recursive_folder rf
JOIN folder_items top3 ON rf.top3_id = top3.id
-- 关联你的项目业务表
JOIN projects p ON rf.item_id = p.id
WHERE rf.depth > 3
GROUP BY top3.id;

注意事项 #

• 层级起始值：如果你的顶层文件夹depth是从0开始（比如parent_id=0代表顶层），记得调整锚点的depth为0，同时CASE语句里的判断条件也要对应修改（比如parent.depth < 2，因为depth=2就是第3层）。
• 性能优化：如果数据量很大，确保parent_id和id字段有索引，避免递归查询速度过慢。

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