Oracle recursive query

-create data

1.1. Create tables and insert data

CREATE TABLE DISTRICT (ID NUMBER (10) NOT NULL, PARENT_ID NUMBER (10), NAME VARCHAR2 (255BYTE) 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') Insert into DISTRICT (id, parent_id, name) values (2,1, 'Bazhong City'); insert into DISTRICT (id, 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, 'Sanhe Township'); insert into DISTRICT (id, parent_id, name) values (11,8, 'Hujia Town') Insert into DISTRICT (id, parent_id, name) values (12,8, 'Nanba Town'); insert into DISTRICT (id, parent_id, name) values (13,6, 'Dazhai Township'); insert into DISTRICT (id, parent_id, name) values (14,6, 'Xiangtan Town'); insert into DISTRICT (id, parent_id, name) values (15,6, 'Longgang Town'); insert into DISTRICT (id, parent_id, name) values (16,6, 'Baiyi Town'); commit

2. Start with connect by prior recursion

Query all child nodes

SELECT *

FROM district

START WITH NAME = 'Bazhong City'

CONNECT BY PRIOR ID=parent_id

2.2. Query all parent nodes

SELECT *

FROM district

START WITH NAME = 'Pingchang County'

CONNECT BY PRIOR parent_id=ID

Just swap the location of id and parent_id

2.3. Query the root node of the specified node

The result of SELECT D. d.parent_id is null is CONNECT BY PRIOR d.ID=d.parent_id of Sichuan Province, connect_by_root (NAME) FROM district dWHERE NAME=' 'START WITH d.parent_id=1-d.parent_id is null of Pingchang County

2.4. Query the recursive path of the administrative organization under Bazhong City.

SELECT ID,parent_id,NAME,sys_connect_by_path (NAME,'- >') namepath,LEVELFROM district START WITH NAME=' Bazhong City 'CONNECT BY PRIOR ID=parent_id

3. With recursion

3.1Recursive subclass of with

WITH t (ID, parent_id,NAME)-- to have the column name AS (SELECT ID, parent_id,NAME FROM district WHERE NAME=' Bazhong 'UNION ALLSELECT d.ID, d.parentprincipiddidNAME FROM ttraining)-- to specify tables and lists, WHERE t.id=d.parent_id) SELECT * FROM t

3.2. Recursive parent class

WITH t (ID, parent_id,NAME)-- to have a table AS (SELECT ID, parent_id,NAME FROM district WHERE NAME=' Tongjiang County 'UNION ALLSELECT d.ID, d.parentmemorid.name FROM ttraining)-- to specify tables and lists, WHERE t.parent_id=d.id) SELECT * FROM t