oracle递归

来源：互联网发布：穿越火线数据异常36_2 编辑：程序博客网时间：2024/05/17 15:38

建表语句：

create table node(id integer primary key,name varchar2(20),father_id integer );insert into node(id,name,father_id) values(1,'1_node',null);insert into node(id,name,father_id) values(11,'11_node',1);insert into node(id,name,father_id) values(12,'12_node',1);insert into node(id,name,father_id) values(121,'121_node',12);insert into node(id,name,father_id) values(122,'122_node',12);insert into node(id,name,father_id) values(1221,'1221_node',122);insert into node(id,name,father_id) values(12211,'12211_node',1221);insert into node(id,name,father_id) values(12212,'12212_node',1221);

查询某节点的所有子孙节点（包括该节点）

SELECT *  FROM node  START WITH id=122  CONNECT BY PRIOR id=father_id;

结果：

ID NAME FATHER_ID
--------------------------------------- -------------------- ---------------------------------------
122 122_node 12
1221 1221_node 122
12211 12211_node 1221
12212 12212_node 1221

注：如果想剔除该节点将上面的sql语句中的id=122改成father_id=122.

查询某节点的所有祖先节点（包括该节点）

SELECT *  FROM node  START WITH id=122  CONNECT BY PRIOR father_id=id;

结果：

ID NAME FATHER_ID
--------------------------------------- -------------------- ---------------------------------------
122 122_node 12
12 12_node 1
1 1_node
注：如果想剔除该节点将上面的sql语句中的id=122改成id=(SELECT father_id FROM node where id=122)。

CONNECT BY子句实列的应用

LEVEL:查询节点层次，从1开始。CONNECT_BY_ISLEAF：查询节点是否是叶子节点，是则为1，不是则为0SELECT id,name,father_id,LEVEL,CONNECT_BY_ISLEAFFROM     node  START WITH id=122   CONNECT BY PRIOR id=father_id  ORDER BY ID;

结果：

ID 　　　　　　　　NAME FATHER_ID 　LEVEL 　　　CONNECT_BY_ISLEAF
--------------------------------------- -------------------- --------------------------------------- 　　---------- 　　　-----------------
122 　　　　122_node 12 　　1 0
1221 　　　1221_node 122 　2 0
12211 　　12211_node 1221 　3 1
12212 　　12212_node 1221 　3 1

查询递归路径

SELECT id,name,father_id, SUBSTR(SYS_CONNECT_BY_PATH(name,'->'),3)pathFROM  node START   WITH id=122   CONNECT BY PRIOR id=father_id;

结果：

ID NAME FATHER_ID PATH
--------------------------------------- -------------------- --------------------- -----------------------------------------------
122 122_node 12 122_node
1221 1221_node 122 122_node->1221_node
12211 12211_node 1221 122_node->1221_node->12211_node
12212 12212_node 1221 122_node->1221_node->12212_node

1 0