Oracle学习：结构化查询实例演示与解析-技术开发专区

Oracle学习：结构化查询实例演示与解析

作者：Snowtoday 编辑：苏巧红 2011-12-13 18:02 来源：博客

　　【IT168技术】一、准备测试数据

　　建立一张简单的职工表 t_hierarchical:

　　|-Emp 职工编号
　　|-Mgr 职工的直接上司(Mgr本身也是职工)
　　|-Emp_name 职工姓名

　　插入一些测试数据，除了大老板AA，其它的职工都各有自己的Manager。

select emp, mgr, emp_name from t_hierarchical t;

1            AA
2     1     BB
3     2     CC
4     3     DD
5     2     EE
6     3     FF

　　二、CONNECT BY

select emp, mgr, LEVEL from t_hierarchical t
CONNECT BY PRIOR emp=mgr
order by emp;

1           1
2     1     2
2     1     1
3     2     1
3     2     3
3     2     2
4     3     4
4     3     1
4     3     2
4     3     3
5     2     3
5     2     2
5     2     1
6     3     2
6     3     3
6     3     4
6     3     1

　　解释一下，CONNECT BY用于指定父-子记录的关系(PRIOR我们在下例中解释，更直观一些)。举emp 2为例，他隶属于emp 1，如果我们以emp 1为根节点，显然LEVEL=2;以emp 2自身为根节点，则LEVEL=1，这就是为什么上述查询结果中出现共色标识部分那两行记录，其它的类推。

　　三、START WITH

select emp, mgr, LEVEL from t_hierarchical t
START WITH emp=1
CONNECT BY PRIOR emp=mgr;

1            1
2     1     2
3     2     3
4     3     4
6     3     4
5     2     3

　　通常我们需要更直观、更具有实用性的结果，这需要用到结构化查询中的START WITH子句，用于指定根节点：

　　这里我们指定了根节点是emp 1，这样的结果直观了许多，例如，以emp 1为根节点，那么emp 3位于第三级(emp 1—emp 2—emp 3)，这里补充一下 PRIOR 关键字的说明，个人观点：“PRIOR emp=mgr”表示前一条记录的emp编号 = 当前记录的mgr编号，从查询结果中可以看出这一点。同时，从查询结果中还能发现明显的递归痕迹，参见不同颜色标识的数字。

　　四、SYS_CONNECT_BY_PATH()

　　不得不介绍一下非常牛波依的SYS_CONNECT_BY_PATH()函数，我们可以得到层次结构或者说树状结构的路径，参见如下：

select emp, mgr, LEVEL, SYS_CONNECT_BY_PATH(emp,'/') path from t_hierarchical t
START WITH emp=1
CONNECT BY PRIOR emp=mgr;

1            1     /1
2     1     2     /1/2
3     2     3     /1/2/3
4     3     4     /1/2/3/4
6     3     4     /1/2/3/6
5     2     3     /1/2/5

　　五、CONNECT_BY_ISLEAF

　　非常好用的CONNECT_BY_ISLEAF虚列。何谓LEAF(叶子)，即没有任何节点隶属于该节点：

select emp, mgr, LEVEL, SYS_CONNECT_BY_PATH(emp,'/') path from t_hierarchical t
where CONNECT_BY_ISLEAF=1
START WITH emp=1
CONNECT BY PRIOR emp=mgr;

4     3     4     /1/2/3/4
6     3     4     /1/2/3/6
5     2     3     /1/2/5

第1页：准备测试数据和相关函数介绍第2页：CONNECT BY与WHERE子句

关注我们