Oracle Handbook系列之结构化查询(1)
一)准备测试数据
闲话少说,直入正题。建立一张简单的职工表 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
通常我们需要更直观、更具有实用性的结果,这需要用到结构化查询中的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
这里我们指定了根节点是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
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。