什么是层次/树型是什么概念?
If a table contains hierarchical data, then you can select rows in a hierarchical. (单击查看内容出处)
注:在Oracle里称为hierarchical queries,而为了方便理解,把它译为层次/树型查询。
作用 & 特征
通常用于查询整个层次/树型关系的数据。如:公司的架构体系里面,表里存储了每一层职位的信息,需要全部查出来;(树的形式) 一些顺序型的记录,像:A群由上海搬到北京,之后又搬到广州,不久又搬到南京…。(单链表形式)
凡是在表里存在以上形式的数据,都可以根据一个点,它所有关联的数据通过一条SELECT语句一次过查询出来。
示例
网上有太多的层次/树型结构的例子,而且在Oracle官方文档里也有很好的例子(单击查看文档例子)。因此本文从数据结构的角度去说明一下具体的用法,不涉及任何业务知识。
先看一张关系图:
至于上图的树型,我们通常说成:1 是根节点;4的父节点是2,子节点有5、6;而像3、5、6…这些没有子节点的节点称为叶结点。(不同的描述有不同的说法,在文章继续之前先统一一下说法)
用表把上面的图存起来。
SQL代码 - CREATE TABLE t_tree(fnum NUMBER, fparent NUMBER);
- INSERT INTO t_tree(fnum, fparent) VALUES(1, null);
- INSERT INTO t_tree(fnum, fparent) VALUES(2, 1);
- INSERT INTO t_tree(fnum, fparent) VALUES(3, 2);
- INSERT INTO t_tree(fnum, fparent) VALUES(4, 2);
- INSERT INTO t_tree(fnum, fparent) VALUES(5, 4);
- INSERT INTO t_tree(fnum, fparent) VALUES(6, 4);
- INSERT INTO t_tree(fnum, fparent) VALUES(7, 1);
- INSERT INTO t_tree(fnum, fparent) VALUES(8, 7);
- INSERT INTO t_tree(fnum, fparent) VALUES(9, 1);
- INSERT INTO t_tree(fnum, fparent) VALUES(10, 9);
- INSERT INTO t_tree(fnum, fparent) VALUES(11, 10);
- INSERT INTO t_tree(fnum, fparent) VALUES(12, 9);
- COMMIT;
|
根据需求写SQL:
查出 1 下面的所有成员:(顺推)
SQL代码 - SELECT fparent
- ,fnum
- ,LEVEL AS n_level
- ,sys_connect_by_path(fnum, '/') AS path
- ,connect_by_isLeaf AS "isLeaf"
- FROM t_tree
- START WITH fparent = 1
- CONNECT BY PRIOR fnum = fparent
结果 - FPARENT FNUM N_LEVEL PATH isLeaf
-
- 1 2 1 /2 0
- 2 3 2 /2/3 1
- 2 4 2 /2/4 0
- 4 5 3 /2/4/5 1
- 4 6 3 /2/4/6 1
- 1 7 1 /7 0
- 7 8 2 /7/8 1
- 1 9 1 /9 0
- 9 10 2 /9/10 0
- 10 11 3 /9/10/11 1
- 9 12 2 /9/12 1
-
- rows selected
|
查出 8 上面的所有成员:(逆推)
SQL代码 - SELECT fparent
- ,fnum
- ,LEVEL AS n_level
- ,sys_connect_by_path(fnum, '/') AS path
- ,connect_by_isLeaf AS "isLeaf"
- FROM t_tree
- START WITH fnum = 8
- CONNECT BY fnum = PRIOR fparent
结果 - FPARENT FNUM N_LEVEL PATH isLeaf
-
- 7 8 1 /8 0
- 1 7 2 /8/7 0
- 1 3 /8/7/1 1
|
查出所有叶(Leaf)结点,没有子节点的节点:
SQL代码 - SELECT *
- FROM t_tree tr
- WHERE 0 = (SELECT COUNT(1)
- FROM t_tree
- WHERE fparent = tr.fnum)
- ORDER BY fnum
结果 - FNUM FPARENT
-
- 3 2
- 5 4
- 6 4
- 8 7
- 11 10
- 12 9
|
其它说明
调优
内部不断根据子、父节点来查询本表。数据量大的话,给表添加子、父节点两个字段的索引,会有很明显的快速效果。
根节点
Oracle 提供了可以快速找到根节点的操作符,如下SQL:
SQL代码 - SELECT fparent
- ,fnum
- ,LEVEL AS n_level
- ,sys_connect_by_path(fnum, '/') AS path
- ,connect_by_root fparent AS root_top
- FROM t_tree
- START WITH fparent = 9
- CONNECT BY PRIOR fnum = fparent
结果 - FPARENT FNUM N_LEVEL PATH ROOT_TOP
-
- 9 10 1 /10 9
- 10 11 2 /10/11 9
- 9 12 1 /12 9
|
如果指定一个节点开始查的话,ROOT就是指定的那个节点,直接用 START WITH 后面那个值就行。不过如果没有指定一个节点,表里有多棵树,这个就很有用了。
循环关系
在记录里有循环关系,树型有根和子节点,而没有循环的关系。这里简单说明一下,循环是怎回事吧。如添加一条这样的记录:
SQL代码 - INSERT INTO t_tree(fnum, fparent) VALUES(7, 8);
|
在1-7-8这条路径制造一条循环记录。之后从8开始逆推到上一层(会报错):
SQL代码 - SQL> SELECT fparent
- 2 ,fnum
- 3 ,LEVEL AS n_level
- 4 ,sys_connect_by_path(fnum, '/') AS path
- 5 ,connect_by_isLeaf AS "isLeaf"
- 6 FROM t_tree
- 7 START WITH fnum = 8
- 8 CONNECT BY fnum = PRIOR fparent
- 9 /
-
- SELECT fparent
- ,fnum
- ,LEVEL AS n_level
- ,sys_connect_by_path(fnum, '/') AS path
- ,connect_by_isLeaf AS "isLeaf"
- FROM t_tree
- START WITH fnum = 8
- CONNECT BY fnum = PRIOR fparent
-
- ORA-01436: 用户数据中的 CONNECT BY 循环
-
- SQL>
|
这里需要用到一个循环处理,如下:
SQL代码 - SQL> SELECT fparent
- 2 ,fnum
- 3 ,LEVEL AS n_level
- 4 ,sys_connect_by_path(fnum, '/') AS path
- 5 ,connect_by_isLeaf AS "isLeaf"
- 6 ,connect_by_isCycle AS "isCycle"
- 7 FROM t_tree
- 8 START WITH fnum = 8
- 9 CONNECT BY NOCYCLE fnum = PRIOR fparent
- 10 /
-
- FPARENT FNUM N_LEVEL PATH isLeaf isCycle
-
- 7 8 1 /8 0 0
- 1 7 2 /8/7 0 0
- 1 3 /8/7/1 1 0
- 8 7 2 /8/7 1 1
|