使用CONNECT BY获取层次结构查询的每个级别的计数/总计 [英] Getting counts/totals at each level of a hierarchical query using CONNECT BY
问题描述
我在这方面有一段时光.我正在尝试针对具有递归关系(分层)的表编写查询(使用Oracle)，并获取存储在树中每个节点及其下方的另一个表中的记录总数.另一个表仅具有与叶节点关联的记录.但是，我想获得树中每个节点及其以下的总计.例如，假设我有两个表. DIRS包含目录名称和标识目录结构的递归关系，而FILES包含带有DIRS外键的文件信息，指示该文件所在的目录:
I am having a heck of a time with this. I am trying to write a query (using Oracle), against a table with a recursive relationship (hierarchical) and get the total number of records stored in another table at and below each node in the tree. The other table only has records associated with the leaf nodes. However, I want to get totals at and below each node in the tree. For example, say I have two tables. DIRS contains the directory names and a recursive relationship identifying the structure of the directories, and FILES contains file information with a foreign key to DIRS indicating the directory the file resides in:
DIRS
====
DIR_ID
PARENT_DIR_ID
DIR_NAME
FILES
=====
FILE_ID
FILE_NAME
DIR_ID
FILE_SIZE
如果DIRS包含:
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
文件包含
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
我想要一个查询，该查询返回路径以及层次结构中每个节点之中或之下的文件数.基本上是文件数量的汇总.因此查询结果将类似于:
I want a query that returns the path along with the number of files in or below each node in the hierarchy. Basically a rollup of the number of files. So the query result would look something like:
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
UPDATE SQL脚本创建带有示例数据的表以符合上述要求:
UPDATE SQL script to create the tables with example data to match the above:
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;
推荐答案
这很简单:
09:38:54 HR@vm_xe> l
1 select sys_connect_by_path(dp.dir_name, '/') path
2 ,(select count(file_id)
3 from dirs dc
4 ,files f
5 where f.dir_id(+) = dc.dir_id
6 connect by prior dc.dir_id = dc.parent_dir_id
7 start with dc.dir_id = dp.dir_id
8 ) count
9 from dirs dp
10 connect by prior dp.dir_id = dp.parent_dir_id
11* start with dp.parent_dir_id is null
09:38:55 HR@vm_xe> /
PATH COUNT
------------------------------ ----------
/ROOT 6
/ROOT/DIR1_1 4
/ROOT/DIR1_1/DIR2_1 1
/ROOT/DIR1_1/DIR2_2 3
/ROOT/DIR1_2 2
5 rows selected.
Elapsed: 00:00:00.02
这篇关于使用CONNECT BY获取层次结构查询的每个级别的计数/总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
