1.如何正确排序: siblings
2.展现路径: SYS_CONNECT_BY_PATH
3.条件的执行顺序
先准备一下测试用的数据:
下面偶来讲具体内容:
1.正确排序: siblings
我们通常使用order by进行排序:
FROM tmp_test2
START WITH FUNC_ID = 1
CONNECT BY PRIOR FUNC_ID = SUPER_ID
order by order_id;
但是这得到的结果是不正确的,因为order by在oracle的sql执行引擎中是最后被执行,其结果如下:
FUNC_ID | SUPER_ID | NAME | ORDER_ID |
1 | 0 | 根配置 | 0 |
12 | 1 | 配置2 | 1 |
14 | 112 | 具体配置分支1 | 3 |
112 | 11 | 具体配置2 | 5 |
111 | 11 | 具体配置1 | 6 |
115 | 112 | 具体配置分支2 | 7 |
116 | 11 | 具体配置3 | 8 |
117 | 116 | 具体配置分支3 | 9 |
128 | 12 | 具体配置11 | 10 |
123 | 12 | 具体配置12 | 14 |
11 | 1 | 配置1 | 21 |
要得到正确的结果集,需要引入siblings,其在oracle的sql执行引擎的递归过程中发挥作用,因此结果正确,使用sql和查询结果如下:
2 FROM tmp_test2
3
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
4
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
5
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
FUNC_ID | SUPER_ID | NAME | ORDER_ID |
1 | 0 | 根配置 | 0 |
12 | 1 | 配置2 | 1 |
128 | 12 | 具体配置11 | 10 |
123 | 12 | 具体配置12 | 14 |
11 | 1 | 配置1 | 21 |
112 | 11 | 具体配置2 | 5 |
14 | 112 | 具体配置分支1 | 3 |
115 | 112 | 具体配置分支2 | 7 |
111 | 11 | 具体配置1 | 6 |
116 | 11 | 具体配置3 | 8 |
117 | 116 | 具体配置分支3 | 9 |
2.展现路径: SYS_CONNECT_BY_PATH
有时候我们需要通过sql来直接展示节点的层次结构,一般的做法是通过伪列level和lpad来构造分割符来构造树的视觉效果,结果类似以下列表:
根配置 |
配置2 |
具体配置11 |
具体配置12 |
配置1 |
具体配置2 |
具体配置分支1 |
具体配置分支2 |
具体配置1 |
具体配置3 |
具体配置分支3 |
其实oracle提供了原生的方法支持此类需求,而且效果更好,那就是函数sys_connect_by_path,使用sql和结果如下:
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
FUNC_ID | SUPER_ID | NAME | ORDER_ID | remark |
1 | 0 | 根配置 | 0 | 根配置 |
12 | 1 | 配置2 | 1 | 根配置->配置2 |
128 | 12 | 具体配置11 | 10 | 根配置->配置2->具体配置11 |
123 | 12 | 具体配置12 | 14 | 根配置->配置2->具体配置12 |
11 | 1 | 配置1 | 21 | 根配置->配置1 |
112 | 11 | 具体配置2 | 5 | 根配置->配置1->具体配置2 |
14 | 112 | 具体配置分支1 | 3 | 根配置->配置1->具体配置2->具体配置分支1 |
115 | 112 | 具体配置分支2 | 7 | 根配置->配置1->具体配置2->具体配置分支2 |
111 | 11 | 具体配置1 | 6 | 根配置->配置1->具体配置1 |
116 | 11 | 具体配置3 | 8 | 根配置->配置1->具体配置3 |
117 | 116 | 具体配置分支3 | 9 | 根配置->配置1->具体配置3->具体配置分支3 |
这点其实在“ 递归查询遍历详解”已经提及了,但是有兄弟经常会搞错
首先是要注意子句的语法书写顺序: select -> from -> where -> start with -> connect by -> order by
where写在connect by后面就不行,报错。
其次要注意子句的执行顺序:from -> start with -> connect by -> where -> select -> order by
执行顺序where在connect by之后,因此如果需要过滤出数据在进行递归查询,一定要将放到一个子查询结果中才行
4、level
例子 create table tmp_test
(id number,
name varchar2(20),
pid number);
插入一些数据 ,如
1 A 0
2 B 1
3 C 2
4 D 3
5 E 3
6 F 4
7 G 5
8 H 1
select a.*,level from tmp_test a
start with id=1
connect by prior id=pid ;
执行结果
ID NAME PID LEVEL
1 A 0 1
2 B 1 2
3 C 2 3
4 D 3 4
6 F 4 5
5 E 3 4
7 G 5 5
8 H 1 2