Oracle recursive query

Creating tables and primary foreign keys

CREATE TABLE sc_district (  ID number         () not                  NULL,  parent_id number  (Ten),  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));

Inserting data

INSERT into a_district (ID, parent_id, name) VALUES (1, NULL, ' Henan Province '); INSERT into a_district (ID, parent_id, name) VALUES ( 2, NULL, ' Shandong Province '); INSERT into a_district (ID, parent_id, name) VALUES (3, 1, ' Zhengzhou '); INSERT into a_district (ID, parent_id, n AME) VALUES (4, 1, ' Kaifeng '), insert into a_district (ID, parent_id, name) VALUES (5, 1, ' Shangqiu '); INSERT into a_district (ID, PA RENT_ID, name) VALUES (6, 3, ' Zhongyuan '); INSERT into a_district (ID, parent_id, name) VALUES (7, 3, ' Jinshui '); INSERT INTO A_distri CT (ID, parent_id, name) VALUES (8, 3, ' Erqi District '); INSERT into a_district (ID, parent_id, name) VALUES (9, 3, ' Chengguan Hui District '); Insert I Nto a_district (ID, parent_id, name) VALUES (10, 3, ' upper block '), insert into a_district (ID, parent_id, name) VALUES (11, 3, ' Megumi  INSERT into a_district (ID, parent_id, name) VALUES (12, 4, ' Kaifeng County ') and insert into a_district (ID, parent_id, name) values (13, 4, ' Qixian '); INSERT into a_district (ID, parent_id, name) VALUES (14, 4, ' Lankao County '); INSERT into a_district (ID, parent_id, n AME) VALUES (15, 4, ' Tongxu CountyINSERT into a_district (ID, parent_id, name) VALUES (16, 13, ' Chengguan Town '); INSERT into a_district (ID, parent_id, name) values (17, 13, ' Five Miles River Town '); INSERT into a_district (ID, parent_id, name) VALUES (18, 13, ' Xing Kou zhen '); INSERT into a_district (ID, parent_i D, name) VALUES (19, 13, ' Persimmon Township '); INSERT into a_district (ID, parent_id, name) VALUES (20, 13, ' Cheng Jiao ');

Generate data as follows

Recursive syntax and primary usage functions

SELECT * FROM table start with conditional entry connect by Prior ID = parent_id (down)

SELECT * FROM table start with conditional entry connect by prior parent_id = ID (UP)

If you do not write prior, it indicates that the traversal direction is not indicated by the pre-order traversal, so recursion is not done, and the order by sort and group by group are customarily followed by the statement.

Connect_by_root (column name) query root node

Connect_by_isleaf query whether the leaf node, 0 is, 1 is not

Sys_connect_by_path Query Recursive path

Actual Combat query root node

Select ID, parent_id, name from a_district start with parent_id are null connect by id = parent_id ORDER by ID

Recursively queries all ancestor nodes of the Qixian (containing the current node)

Select ID, parent_id, name from a_district start with name= ' Qixian ' connect by prior parent_id = ID ORDER by ID

Recursively queries all ancestor nodes of Qixian (contains current node and root node)

Select ID, parent_id, name,connect_by_root (ID) city_id, connect_by_root (name) City_name from A_district        start with Name= ' Qixian ' connect by prior parent_id = ID ORDER by ID

Recursive query Qixian all child towns and villages (including current node)

Select ID, parent_id, name from a_district start with name= ' Qixian ' connect by Prior ID = parent_id ORDER by ID

Recursively query all towns and villages in Kaifeng (contains current node and root node)

Select ID, parent_id, name, Connect_by_root (ID) city_id, connect_by_root (name) City_name from A_district        start with< C1/>name= ' Kaifeng ' connect by Prior ID = parent_id ORDER by ID

Upstream recursive query the depth of the Cheng Jiao, whether it contains leaf nodes

Select ID, name, parent_id, level, Connect_by_isleaf isleaf from a_district start with name = ' Cheng Jiao ' connect by prior Paren t_id = ID ORDER by ID

Downstream recursive query Henan province depth, whether the leaf node is included

Select ID, name, parent_id, level, Connect_by_isleaf isleaf from a_district start with name = ' Henan ' connect by Prior ID = PARENT_ID ORDER BY ID

Upstream recursive query Qixian path

Select ID, name, parent_id, substr (Sys_connect_by_path (name, ', '), 3) Name_path from A_district        start with Name= ' Qixian ' Connect by prior parent_id = ID ORDER by ID

downlink recursive query Henan province path

Select ID, name, parent_id, substr (Sys_connect_by_path (name, ', '), 3) Name_path from A_district        start with Name= ' Henan ' Connect by Prior ID = parent_id ORDER by ID

Integrated use

Select ID, name, parent_id, level, Connect_by_isleaf isleaf, substr (Sys_connect_by_path (name, '-I '), 3) Name_path from A_district        start with name= ' Henan ' connect by Prior ID = parent_id ORDER by ID

Oracle recursive query