Oracle递归查询详解_oracle 递归包括当前节点-CSDN博客

本文链接：https://blog.csdn.net/lgq_0714/article/details/26614667

Oracle递归语法：

select * from table_name where cond1..... start with cond2.... connect by prior cond3........

例如：select * from SC_DISTRCT WHERE ID<>1 START WITH ID=1 CONNECT BY PRIOR ID=PARENT_ID

创建表

建表语句：

CREATE TABLE SC_DISTRICT
(
  ID         NUMBER(10)                  NOT NULL,
  PARENT_ID  NUMBER(10),
  NAME       VARCHAR2(255 BYTE)          NOT NULL
);

ALTER TABLE SC_DISTRICT ADD (
  CONSTRAINT SC_DISTRICT_PK
 PRIMARY KEY
 (ID));

ALTER TABLE SC_DISTRICT ADD (
  CONSTRAINT SC_DISTRICT_R01 
 FOREIGN KEY (PARENT_ID) 
 REFERENCES SC_DISTRICT (ID));

插入数据:

INSERT INTO SC_DISTRICT(ID,NAME) VALUES(1,'四川省');

INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(2,1,'巴中市');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(3,1,'达州市'); 

INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(4,2,'巴州区');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(5,2,'通江县');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(6,2,'平昌县');

INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(7,3,'通川区');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(8,3,'宣汉县');

INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(9,8,'塔河乡');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(10,8,'三河乡');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(11,8,'胡家镇');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(12,8,'南坝镇');
 
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(13,6,'大寨乡');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(14,6,'响滩镇');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(15,6,'龙岗镇');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(16,6,'白衣镇');

生成表如下：

查询某节点的所有子孙节点

查询巴中市下面的所有行政组织(结果包含当前节点):
SELECT *
FROM SC_DISTRICT
START WITH NAME='巴中市'
CONNECT BY PRIOR ID=PARENT_ID

查询结果:
ID    PARENT_ID      NAME
2    	1            巴中市
4    	2            巴州区
5    	2            通江县
6    	2            平昌县
13    	6            大寨乡
14    	6            响滩镇
15    	6            龙岗镇
16    	6            白衣镇

查询指定节点的递归根节点

查询响滩镇镇所属的市：
SELECT 	ID,

	PARENT_ID,

	NAME,     
       	CONNECT_BY_ROOT(ID)   CITY_ID,
       	CONNECT_BY_ROOT(NAME) CITY_NAME
FROM   SC_DISTRICT
WHERE  NAME='响滩镇' 
       START WITH PARENT_ID=1
       CONNECT BY PRIOR ID=PARENT_ID
查询结果：
ID        PARENT_ID    NAME    CITY_ID    CITY_NAME
14        6            响滩镇    2          巴中市

CONNECT BY子句伪列的应用

LEVEL:查询节点层次，从1开始。
CONNECT_BY_ISLEAF：查询节点是否是叶子节点，是则为1，不是则为0
SELECT  ID,

	NAME,

	PARENT_ID,

	LEVEL,

	CONNECT_BY_ISLEAF
FROM     SC_DISTRICT
         START WITH NAME='巴中市'
         CONNECT BY PRIOR ID=PARENT_ID 
         ORDER BY ID;

查询结果：
ID    NAME    PARENT_ID    LEVEL    CONNECT_BY_ISLEAF
2     巴中市    1           1    	0
4     巴州区    2           2    	1	
5     通江县    2           2    	1
6     平昌县    2           2    	0
13    大寨乡    6           3    	1
14    响滩镇    6           3    	1
15    龙岗镇    6           3    	1
16    白衣镇    6           3      	1

查询递归路径

查询巴中市下行政组织递归路径
SELECT  ID,

	NAME,

	PARENT_ID,
        SUBSTR(SYS_CONNECT_BY_PATH(NAME,'->'),3)   NAME_PATH
FROM    SC_DISTRICT
        START   WITH NAME='巴中市'
        CONNECT BY PRIOR ID=PARENT_ID
        
查询结果：
ID    NAME    PARENT_ID    NAME_PATH
2     巴中市    1           巴中市
4     巴州区    2           巴中市->巴州区
5     通江县    2           巴中市->通江县
6     平昌县    2           巴中市->平昌县
13    大寨乡    6           巴中市->平昌县->大寨乡
14    响滩镇    6           巴中市->平昌县->响滩镇
15    龙岗镇    6           巴中市->平昌县->龙岗镇
16    白衣镇    6           巴中市->平昌县->白衣镇

SYS_CONNECT_BY_PATH函数：

   
   
    
    
     
     
      
      
       
       在Oracle中，SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。
       
       SYS_CONNECT_BY_PATH(字段名, 2个字段之间的连接符号)，注意这里的连接符号不要使用逗号，Oracle会报错，如果一定要用，可以使用replace替换一下，方法如下 REPLACE(字段名,原字符,',')。 
      
      
      
      
       
       还有，这个函数使用之前必须先建立一个树，否则无用。

oracle 正向递归和反向递归查询
--正向递归查询（根据id查询自己和自己一下的数据）
select * from SC_DISTRICT
	start with id=1
	connect by prior ID= parent_Id
order by id
解释：获取ID为1的子节点
</pre><span style="font-family:Tahoma; font-size:14px; line-height:24px">--反向递归查询(根据叶子ID查出自己和自己之上的根数据)</span><br style="margin:0px; padding:0px; font-family:Tahoma; font-size:14px; line-height:24px" /><pre code_snippet_id="358853" snippet_file_name="blog_20140522_4_4753316" class="java" name="code" style="margin-top: 0px; margin-bottom: 0px; padding: 0px; font-size: 14px; line-height: 24px; ">select * from SC_DISTRICT
	start with id=16
	connect by nocycle prior parent_Id=ID
order by id
解释：获取ID为16的所有父节点