首页 > 代码库 > Oracle start with connect by prior... 递归查询

Oracle start with connect by prior... 递归查询

start with connect by prior 主要是用于B树结构类型的数据递归查询,给出B树结构类型中的任意一个节点,遍历其最终父节点或者子节点。

-- create table
create table prior_test
( parentid number(10),
  subid    number(10)
);
-- 字段类型最好用 number,而不是 varchar2,因为测试 SQL 需要比较 id

-- insert
insert into prior_test values ( 1, 2 );
insert into prior_test values ( 1, 3 );
insert into prior_test values ( 2, 4 );
insert into prior_test values ( 2, 5 );
insert into prior_test values ( 3, 6 );
insert into prior_test values ( 3, 7 );
insert into prior_test values ( 5, 8 );
insert into prior_test values ( 5, 9 );
insert into prior_test values ( 7, 10 );
insert into prior_test values ( 7, 11 );
insert into prior_test values ( 10, 12 );
insert into prior_test values ( 10, 13 );

 

-- select
select * from prior_test

技术分享

 

-- SQL-1
select t.parentid, t.subid, level 
from prior_test t
start with t.subid = 7
connect by subid = prior parentid
order by level desc

技术分享

 

-- SQL-2
select t.parentid, t.subid, level 
from prior_test t
start with t.subid = 7
connect by prior subid = parentid
order by level desc

技术分享

SQL 解析:

start with 子句:遍历起始条件

connect by 子句:连接条件

  关键词 prior,prior 跟父节点列 parentid 放在一起,prior parentid 就是往父节点方向遍历;prior跟子节点列 subid 放在一起,prior subid 则往子节点方向遍历。

  parentid、subid 两列谁放在 ‘=‘ 前都无所谓,关键是 prior 后面的字段。(比较上面查询语句 SQL-1 与 SQL-2)

order by 子句:排序

 

观察下面 SQL-3 与 SQL-4 分别执行的结果集:

-- SQL-3
select t.parentid, t.subid, level 
from prior_test t
start with t.subid = 7
connect by prior subid = parentid
order by level desc

技术分享

 

-- SQL-4
select t.parentid, t.subid, level 
from prior_test t
start with t.parentid = 7
connect by prior subid = parentid
order by level desc

技术分享

结论:start with subid 与 start with parentid 结果集是不同的。

 

加入 where 子句

-- SQL-5
select t.parentid, t.subid, level from bb_test t
where t.parentid > 3
start with t.subid = 12
connect by subid = prior parentid
order by level desc

技术分享

SQL 执行顺序是:先 start with 子句, 在是 connect by 子句, 最后是 where 子句!

where 只是树结构的修剪,不改变树的层次结构。

Oracle start with connect by prior... 递归查询