Oracle recursive query

----Create data

1.1. Create tables and insert data

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "/>

Create table district (  id         number (10)                    Not null,  parent_id  number (Ten),  name        VARCHAR2 (255 byte)           not null); alter table district add  (  constraint district_pk primary key   (ID)); alter table district add  (  constraint district_r01  foreign  KEY  (parent_id)   REFERENCES DISTRICT  (ID));    insert into  DISTRICT  (Id, parent_id, name) values  (1, null,  ' Sichuan Province '); Insert into  DISTRICT  (Id, parent_id, name) values  (2, 1,  ' Bazhong ');insert into  district  (ID,&NBSp;parent_id, name) values  (3, 1,  ' Dazhou ');insert into district  (id,  Parent_id, name) values  (4, 2,  ' Bazhou District ');insert into district  (id, parent_ Id, name) values  (5, 2,  ' Tongjiang County ');insert into district  (id, parent_id,  name) values  (6, 2,  ' Pingchang County ');insert into district  (id, parent_id,  Name) values  (7, 3,  ' Tongchuan District ');insert into district  (id, parent_id, name) values  (8, 3,  ' Xuanhan County ');insert into district  (id, parent_id, name) values   (9, 8,  ' Tahe township ');insert into district  (id, parent_id, name) values  ( 10, 8,  ' Mikawa Township ');insert into district  (id, parent_id, name) values  (11,  8,  ' Hu Jia Zhen ');insert into district  (id, parent_id, name) values  (12,  8,  ' nan Ba Town '); Insert into district  (Id, parent_id, name) values  (13, 6,  ' da Zhai Xiang '); insert into  DISTRICT  (Id, parent_id, name) values  (14, 6,  ' Ring beach town ');insert into  district  (Id, parent_id, name) values  (15, 6,  ' Longgang Town ');insert into  district  (Id, parent_id, name) values  (16, 6,  ' White Town '); commit;

650) this.width=650; "src=" http://images2015.cnblogs.com/blog/710715/201511/710715-20151113140234853-565754984.jpg "/>

second, start with connect by prior recursion

1. Querying all child nodes
   

SELECT *
From District
START with NAME = ' Bazhong '
CONNECT by PRIOR id=parent_id

650) this.width=650; "src=" http://images2015.cnblogs.com/blog/710715/201511/710715-20151113140429712-390342453.jpg "/>

2.2. Querying all parent nodes

SELECT *
From District
START with NAME = ' Pingchang County '
CONNECT by PRIOR Parent_id=id

You only need to swap the ID with the parent_id location.

650) this.width=650; "src=" http://images2015.cnblogs.com/blog/710715/201511/710715-20151113140553978-1462460137.jpg "/>

2.3. Query the root node of the specified node

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "/>

SELECT D.*,connect_by_root (d.id), Connect_by_root (NAME) from District dwhere name= ' Pingchang County ' START with d.parent_id=1-- D.PARENT_ID is null result for Sichuan connect by PRIOR d.id=d.parent_id

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "/>

650) this.width=650; "src=" http://images2015.cnblogs.com/blog/710715/201511/710715-20151113141339025-664756404.jpg "/>

2.4, query Bazhong the administrative organization recursive path

SELECT Id,parent_id,name,sys_connect_by_path (NAME, ', ') Namepath,levelfrom District START with Name= ' Bazhong ' Connect by PRIOR id=parent_id

650) this.width=650; "src=" http://images2015.cnblogs.com/blog/710715/201511/710715-20151113141811244-1376303505.jpg "/>

third, with recursion

3.1. With recursive sub-class

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "/>

With T (ID, Parent_id,name)--to have column name as (SELECT ID, parent_id,name from District WHERE name= ' Bazhong ' UNION allselect d.id, D.paren T_id,d.name from T,district D--To specify the table and list, where t.id=d.parent_id) SELECT * from T;

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "/>

650) this.width=650; "src=" http://images2015.cnblogs.com/blog/710715/201511/710715-20151113142500759-1277968503.jpg "/>

3.2. Recursive parent class

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "/>

With T (ID, Parent_id,name)--to have table as (SELECT ID, parent_id,name from District WHERE name= ' Tongjiang County ' UNION allselect d.id, d.parent _id,d.name from T,district D--To specify the table and list, where t.parent_id=d.id) SELECT * from T;

650) this.width=650; "src=" Http://common.cnblogs.com/images/copycode.gif "alt=" Copy Code "/>

650) this.width=650; "src=" http://images2015.cnblogs.com/blog/710715/201511/710715-20151113143231431-1925487760.jpg "/>

This article from "There is nothing, know in Providence" blog, please be sure to keep this source http://yangsj.blog.51cto.com/8702844/1714358

Oracle recursive query