Oracle递归查询实战-优快云博客

-- connect by rownum 可以用来构建序列表
select rownum from dual connect by rownum<10;
select rownum from all_objects where rownum<10;
-- connect by level 的分析
CREATE TABLE T
(
  ID  VARCHAR2(1 BYTE)
);
INSERT INTO T ( ID ) VALUES ( 
'A'); 
INSERT INTO T ( ID ) VALUES ( 
'B'); 
INSERT INTO T ( ID ) VALUES ( 
'C'); 
COMMIT;
-- Oracle采用了深度优先遍历算法
-- 当连接条件不能限制记录之间的关系时，每一条记录都可以作为自己或者其他记录的叶子
select id,level from t connect by level<2;
select id,level from t connect by level<3;
select id,level from t connect by level<4;

--递归查询示例：
CREATE TABLE A_DISTRICT  
(  
  ID         NUMBER(10)                  NOT NULL,  
  PARENT_ID  NUMBER(10),  
  NAME       VARCHAR2(255 BYTE)          NOT NULL  
);   
ALTER TABLE A_DISTRICT ADD (  
  CONSTRAINT A_DISTRICT_PK  
 PRIMARY KEY  
 (ID));  
ALTER TABLE A_DISTRICT ADD (  
  CONSTRAINT A_DISTRICT_FK
 FOREIGN KEY (PARENT_ID)   
 REFERENCES A_DISTRICT (ID)  
);  

insert into A_DISTRICT (id, parent_id, name) values (1, null, '河南省');  
insert into A_DISTRICT (id, parent_id, name) values (2, null, '山东省');  
insert into A_DISTRICT (id, parent_id, name) values (3, 1, '郑州市');  
insert into A_DISTRICT (id, parent_id, name) values (4, 1, '开封市');  
insert into A_DISTRICT (id, parent_id, name) values (5, 1, '商丘市');  
insert into A_DISTRICT (id, parent_id, name) values (6, 3, '中原区');  
insert into A_DISTRICT (id, parent_id, name) values (7, 3, '金水区');  
insert into A_DISTRICT (id, parent_id, name) values (8, 3, '二七区');  
insert into A_DISTRICT (id, parent_id, name) values (9, 3, '城管回族区');  
insert into A_DISTRICT (id, parent_id, name) values (10, 3, '上街区');  
insert into A_DISTRICT (id, parent_id, name) values (11, 3, '惠济区');  
insert into A_DISTRICT (id, parent_id, name) values (12, 4, '开封县');  
insert into A_DISTRICT (id, parent_id, name) values (13, 4, '杞县');  
insert into A_DISTRICT (id, parent_id, name) values (14, 4, '兰考县');  
insert into A_DISTRICT (id, parent_id, name) values (15, 4, '通许县');  
insert into A_DISTRICT (id, parent_id, name) values (16, 13, '城关镇');  
insert into A_DISTRICT (id, parent_id, name) values (17, 13, '五里河镇');  
insert into A_DISTRICT (id, parent_id, name) values (18, 13, '邢口镇');  
insert into A_DISTRICT (id, parent_id, name) values (19, 13, '柿园乡');  
insert into A_DISTRICT (id, parent_id, name) values (20, 13, '城郊乡');  
commit;
select * from A_DISTRICT;
/*
递归查询语法：
    select * from 表 start with 条件入口 connect by prior id =  parent_id（向下递归）；
    select * from 表 start with 条件入口 connect by prior parent_id = id（向上递归）；
    如果不写prior，表明前序遍历,未指明遍历方向，所以不进行递归，只会查询符合start with条件的记录；
    习惯上会在语句后面加上order by排序和group by分组；

主要使用函数：
    connect_by_root(列名)  查询根节点
    connect_by_isleaf   查询是否叶子节点，1是,0不是
    sys_connect_by_path  查询递归路径
    
注意：
    sys_connect_by_path函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。
    sys_connect_by_path(字段名, 2个字段之间的连接符号)，注意这里的连接符号不要使用逗号，oracle会报错，如果一定要用，
    可以使用replace替换一下，REPLACE(字段名,原字符,',')。还有，这个函数使用之前必须先建立一个树，否则无用。
*/
-- 查询根节点
select * from a_district start with parent_id is null connect by id=parent_id order by id;
--递归查询'杞县' 的所有上级节点（包含当前节点）
select * from a_district start with name='杞县' connect by prior parent_id=id order by id;
--递归查询'杞县'的所有上级节点（包含当前节点和根节点(因为是向上递归，所以‘杞县’是根节点)）
select id, parent_id, name,connect_by_root(id) city_id, connect_by_root(name) city_name from a_district   
       start with name='杞县' connect by prior parent_id = id order by id;
--递归查询'杞县'的所有子城镇乡（包含当前节点）
select * from a_district  start with name='杞县' connect by prior id = parent_id order by id;
--递归查询'开封市'的所有城镇乡（包含当前节点和根节点(因为是向下递归，所以‘开封市’是根节点)）
select id, parent_id, name,connect_by_root(id) city_id, connect_by_root(name) city_name from a_district   
       start with name='开封市' connect by prior id = parent_id order by id;
--上行递归查询'城郊乡'深度、是否是叶子节点
select id, parent_id, name,level, connect_by_isleaf from a_district   
       start with name='城郊乡' connect by prior parent_id = id order by id;
--下行递归查询'河南省'深度、是否是叶子节点
select id, name, parent_id, level, connect_by_isleaf isleaf from a_district 
       start with name = '河南省' connect by prior id = parent_id order by id;
--上行递归查询'杞县'的递归路径
select id, name, parent_id, substr(sys_connect_by_path(name,'->'),3) name_path from a_district   
       start with name='杞县' connect by prior parent_id = id order by id;  
--下行递归查询'河南省'的递归路径
select id, name, parent_id, substr(sys_connect_by_path(name,'->'),3) name_path from a_district   
       start with name='河南省' connect by prior id = parent_id order by id;  
--综合使用：下行递归查询'河南省'深度、是否是叶子节点、递归路径
select id, name, parent_id, level, connect_by_isleaf isleaf, substr(sys_connect_by_path(name,'->'),3) name_path from a_district  
        start with name='河南省' connect by prior id = parent_id order by id;  

转自：http://www.ibloger.net/article/256.html

-- ------------------------------------------------------------------------------------------------------------------------------------------------

/* oracle递归查询 （适用于含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,'白衣镇');
commit;
select * from sc_district;
/* 查询某节点的所有子孙节点 */
-- 查询巴中市下面的所有行政组织(结果包含当前节点):
SELECT * FROM SC_DISTRICT 
       START WITH NAME='巴中市' 
       CONNECT BY PRIOR ID=PARENT_ID;
/* 查询指定节点的递归根节点 */
-- 查询响滩镇镇所属的市：
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;
/* 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;
/* 查询递归路径 */
-- 查询巴中市下行政组织递归路径
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

转自 http://www.cnblogs.com/wanghonghu/archive/2012/08/31/2665945.html