Oracle层级目录下各目录项目数统计的SQL查询方案咨询

嘿，这个问题属于典型的树形目录结构的递归统计场景，我来给你拆解下怎么用SQL一次性搞定所有目录（含子目录）的项目数统计：

先明确下我们的表结构和需求 #

Directories 目录表

Items 项目表

我们需要统计每个目录及其所有层级子目录下的项目总数，预期结果如下：

解决方案：用递归CTE实现 #

现在主流数据库（MySQL 8.0+/PostgreSQL/SQL Server）都支持递归CTE，这是处理树形结构最简洁的方式。核心思路是先递归遍历每个目录的所有后代，再关联项目表统计数量：

WITH RECURSIVE DirectoryHierarchy AS (
    -- 第一步：锚点查询，把每个目录自身作为根目录的子目录（统计自身的项目）
    SELECT 
        ID AS RootDirID,
        ID AS ChildDirID
    FROM Directories
    UNION ALL
    -- 第二步：递归查询，不断获取子目录，把所有后代目录关联到对应的根目录
    SELECT 
        dh.RootDirID,
        d.ID AS ChildDirID
    FROM DirectoryHierarchy dh
    JOIN Directories d ON dh.ChildDirID = d.Parent
)
-- 第三步：关联统计每个根目录下的所有项目
SELECT 
    d.ID,
    d.Name,
    COUNT(i.ID) AS `Items Count`
FROM Directories d
LEFT JOIN DirectoryHierarchy dh ON d.ID = dh.RootDirID
LEFT JOIN Items i ON dh.ChildDirID = i.Directory
GROUP BY d.ID, d.Name
ORDER BY d.ID;

代码逻辑拆解 #

1. 递归CTE DirectoryHierarchy：
   • 锚点部分：先给每个目录创建一条自身关联的记录，比如目录ID=2（Movies），初始记录是RootDirID=2, ChildDirID=2
   • 递归部分：通过JOIN Directories找到ChildDirID对应的子目录，比如ID=2的子目录是5和6，所以会生成RootDirID=2, ChildDirID=5和RootDirID=2, ChildDirID=6的记录，直到遍历完所有子层级
2. 统计部分：
   • 把目录表和递归生成的层级表关联，得到每个目录对应的所有子目录ID（包括自身）
   • 再关联项目表，统计这些目录下的所有项目数量
   • 用LEFT JOIN保证即使目录下没有项目（比如ID=10的"Beat it"）也会显示0，而不是被过滤掉

特殊情况说明 #

如果你的数据库是MySQL 5.x这类不支持递归CTE的版本，那可以用自连接+用户变量的方式模拟递归，但实现起来比较繁琐，建议优先升级到支持递归CTE的版本，或者参考层级遍历的替代方案。

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