Eu estou tendo um tempo com isso. Eu estou tentando escrever uma consulta (usando Oracle), contra uma tabela com um relacionamento recursivo (hierárquico) e obter o número total de registros armazenados em outra tabela em e abaixo de cada nó na árvore. A outra tabela tem apenas registros associados aos nós folha. No entanto, quero obter totais em e abaixo de cada nó na árvore. Por exemplo, digamos que eu tenha duas tabelas. DIRS contém os nomes de diretório e um relacionamento recursivo que identifica a estrutura dos diretórios, e FILES contém informações de arquivo com uma chave estrangeira para DIRS, indicando o diretório em que o arquivo reside:

DIRS
====
DIR_ID 
PARENT_DIR_ID
DIR_NAME

FILES
=====
FILE_ID
FILE_NAME
DIR_ID
FILE_SIZE

Se DIRS contiver:

DIR_ID   PARENT_DIR_ID   DIR_NAME
======   =============   ========
1                        ROOT
2        1               DIR1_1
3        1               DIR1_2
4        2               DIR2_1
5        2               DIR2_2

e arquivos contém

FILE_ID   FILE_NAME   DIR_ID   FILE_SIZE
=======   =========   ======   =========
1         test1.txt   5        100
2         test2.txt   5        200
3         test5.txt   5         50 
4         test3.txt   3        300
5         test4.txt   3        300
6         test6.txt   4        100

Eu quero uma consulta que retorna o caminho junto com o número de arquivos dentro ou abaixo de cada nó na hierarquia. Basicamente, um acúmulo do número de arquivos. Então, o resultado da consulta seria algo como:

Path                    File_Count
=====                   ===========
/ROOT                   6
/ROOT/DIR1_1            4
/ROOT/DIR1_1/DIR2_1     1
/ROOT/DIR1_1/DIR2_2     3
/ROOT/DIR1_2            2

ATUALIZAR Script SQL para criar as tabelas com dados de exemplo para corresponder ao acima:

create table DIRS (dir_id number(38) primary key
    , parent_dir_id number(38) null references DIRS(dir_id)
    , dir_name varchar2(128) not null);

create table FILES (file_id number(38) primary key
    , file_name varchar2(128) not null
    , dir_id number(38) not null references DIRS(dir_id)
    , file_size number not null
    , unique (dir_id, file_name));

insert into DIRS 
select 1, null, 'ROOT' from dual
union all select 2, 1, 'DIR1_1' from dual 
union all select 3, 1, 'DIR1_2' from dual 
union all select 4, 2, 'DIR2_1' from dual 
union all select 5, 2, 'DIR2_2' from dual;

insert into files
select 1, 'test1.txt', 5, 100 from dual
union all select 2, 'test2.txt', 5, 200 from dual
union all select 3, 'test5.txt', 5, 50 from dual
union all select 4, 'test3.txt', 3, 300 from dual
union all select 5, 'test4.txt', 3, 300 from dual
union all select 6, 'test6.txt', 4, 100 from dual;

commit;