Oracle中的select语句可以用start with...connect by prior子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是:
select ... from
where
start with
connect by ;
:过滤条件,用于对返回的所有记录进行过滤。
:查询结果重起始根结点的限定条件。
:连接条件,其中用PRIOR表示上一条记录,比如 CONNECT BY PRIOR id = root_id就是说上一条记录的id 是本条记录的root_id,即本记录的父亲是上一条记录
运算符PRIOR被放置于等号前后的位置,决定着查询时的检索顺序。
PRIOR被置于CONNECT BY子句中等号的前面时,则强制从根节点到叶节点的顺序检索,即由父节点向子节点方向通过树结构,我们称之为自顶向下的方式。如:
CONNECT BY PRIOR id=root_id,PIROR运算符被置于CONNECT BY 子句中等号的后面时,则强制从叶节点到根节点的顺序检索,即由子节点向父节点方向通过树结构,我们称之为自底向上的方式。例如: CONNECT BY id=PRIOR root_id ,在这种方式中也应指定一个开始的节点。
例:
点击(此处)折叠或打开
- create table tmp(
- root_id number,
- id number,
- name varchar(5),
- description varchar(10)
- );
- insert into tmp(root_id,id,name,description) values(0,1,'a','aaa');
- insert into tmp(root_id,id,name,description) values(1,2,'a1','aaa1');
- insert into tmp(root_id,id,name,description) values(1,3,'a2','aaa2');
- insert into tmp(root_id,id,name,description) values(2,4,'b','bbb');
- insert into tmp(root_id,id,name,description) values(4,5,'b1','bbb1');
- insert into tmp(root_id,id,name,description) values(4,6,'b2','bbb2');
- commit;
-
-
- SQL> select * from tmp;
-
- ROOT_ID ID NAME DESCRIPTIO
- ---------- ---------- ----- ----------
-
- 0 1 a aaa
- 1 2 a1 aaa1
- 1 3 a2 aaa2
- 2 4 b bbb
- 4 5 b1 bbb1
- 4 6 b2 bbb2
-
- 已选择6行。
-
-
- SQL> select * from tmp start with id=6 connect by prior id=root_id;
-
- ROOT_ID ID NAME DESCRIPTIO
- ---------- ---------- ----- ----------
-
- 4 6 b2 bbb2
-
- SQL> select * from tmp start with id=6 connect by id=prior root_id;
-
- ROOT_ID ID NAME DESCRIPTIO
- ---------- ---------- ----- ----------
-
- 4 6 b2 bbb2
- 2 4 b bbb
- 1 2 a1 aaa1
- 0 1 a aaa
-
-
-
- SQL> select * from tmp start with root_id=0 connect by prior id=root_id;
-
- ROOT_ID ID NAME DESCRIPTIO
- ---------- ---------- ----- ----------
-
- 0 1 a aaa
- 1 2 a1 aaa1
- 2 4 b bbb
- 4 5 b1 bbb1
- 4 6 b2 bbb2
- 1 3 a2 aaa2
-
- 已选择6行。
-
- SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21251711/viewspace-1066371/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21251711/viewspace-1066371/