oracle子递归查询语句的几个有用的属性

技术2026-01-01 0

1.如何正确排序: siblings 2.展现路径: SYS_CONNECT_BY_PATH 3.条件的执行顺序

先准备一下测试用的数据：

SELECT FUNC_ID, SUPER_ID, NAME FROM MGR_PURV_FUNCTION START WITH FUNC_ID = 5 CONNECT BY PRIOR FUNC_ID = SUPER_ID FUNC_IDSUPER_IDNAMEORDER_ID10根配置0111配置12111111具体配置1611211具体配置2514112具体配置分支13115112具体配置分支2711611具体配置38117116具体配置分支39121配置2112812具体配置111012312具体配置1214

下面偶来讲具体内容： 1.正确排序: siblings 我们通常使用order by进行排序：

但是这得到的结果是不正确的，因为order by在oracle的sql执行引擎中是最后被执行，其结果如下：

FUNC_IDSUPER_IDNAMEORDER_ID10根配置0121配置2114112具体配置分支1311211具体配置2511111具体配置16115112具体配置分支2711611具体配置38117116具体配置分支3912812具体配置111012312具体配置1214111配置121

要得到正确的结果集，需要引入siblings，其在oracle的sql执行引擎的递归过程中发挥作用，因此结果正确，使用sql和查询结果如下：

SELECT FUNC_ID, SUPER_ID, NAME,ORDER_ID 2 FROM tmp_test2 3 START WITH FUNC_ID = 1 4 CONNECT BY PRIOR FUNC_ID = SUPER_ID 5 order siblings by order_id; FUNC_IDSUPER_IDNAMEORDER_ID10根配置0121配置2112812具体配置111012312具体配置1214111配置12111211具体配置2514112具体配置分支13115112具体配置分支2711111具体配置1611611具体配置38117116具体配置分支39

2.展现路径: SYS_CONNECT_BY_PATH 有时候我们需要通过sql来直接展示节点的层次结构，一般的做法是通过伪列level和lpad来构造分割符来构造树的视觉效果，结果类似以下列表：

根配置配置2 具体配置11 具体配置12 配置1 具体配置2 具体配置分支1 具体配置分支2 具体配置1 具体配置3 具体配置分支3

其实oracle提供了原生的方法支持此类需求，而且效果更好，那就是函数sys_connect_by_path,使用sql和结果如下：

SELECT FUNC_ID, SUPER_ID, NAME, ORDER_ID, SUBSTR(SYS_CONNECT_BY_PATH(NAME, ' -> ' ), 3 ) REMARK FROM TMP_TEST2 START WITH FUNC_ID = 1 CONNECT BY PRIOR FUNC_ID = SUPER_ID ORDER SIBLINGS BY ORDER_ID;

FUNC_ID

SUPER_ID

NAME

ORDER_ID

remark

10根配置0根配置121配置21根配置->配置212812具体配置1110根配置->配置2->具体配置1112312具体配置1214根配置->配置2->具体配置12111配置121根配置->配置111211具体配置25根配置->配置1->具体配置214112具体配置分支13根配置->配置1->具体配置2->具体配置分支1115112具体配置分支27根配置->配置1->具体配置2->具体配置分支211111具体配置16根配置->配置1->具体配置111611具体配置38根配置->配置1->具体配置3117116具体配置分支39根配置->配置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

最新回复(0)