Oracle recursive query

Oracle recursive query
Create a table and a primary foreign key

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 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 City'); insert into A_DISTRICT (id, parent_id, name) values (4, 1, 'kaifeng City'); insert into A_DISTRICT (id, parent_id, name) values (5, 1, 'shangqiu City'); insert into A_DISTRICT (id, parent_id, name) values (6, 3, 'zhongyuan Region'); insert into A_DISTRICT (id, parent_id, name) values (7, 3, 'jinshui'); insert into A_DISTRICT (id, parent_id, name) values (8, 3, 'er7'); insert into A_DISTRICT (id, parent_id, name) values (9, 3, 'urban management Hui Region '); insert into A_DISTRICT (id, parent_id, name) values (10, 3, 'block up'); insert into A_DISTRICT (id, parent_id, name) values (11, 3, 'huiji Region'); insert into A_DISTRICT (id, parent_id, name) values (12, 4, 'kaifeng Region'); insert into A_DISTRICT (id, parent_id, name) values (13, 4, 'jinxian '); insert into A_DISTRICT (id, parent_id, name) values (14, 4, 'lankao '); insert into A_DISTRICT (id, parent_id, name) values (15, 4, 'tongxu County '); insert into A_DISTRICT (id, parent_id, name) values (16, 13, 'chengguan town'); insert into A_DISTRICT (id, parent_id, name) values (17, 13, 'wulihezhen '); insert into A_DISTRICT (id, parent_id, name) values (18, 13, 'hangzhou '); insert into A_DISTRICT (id, parent_id, name) values (19, 13, 'shiyuanxiang '); insert into A_DISTRICT (id, parent_id, name) values (20, 13, 'suburban towns ');

The generated data is as follows:

Recursive syntax and main functions

Select * from Table start with condition entry connect by priZ limit? Http://www.bkjia.com/kf/ware/vc/ "target =" _ blank "class =" keylink "> keys/keys + keys/yyc + jqTwvcD4KPHA + yOe5 + keys/yo6zL + dLUsru9 + keys + ykgILLp0a + 4 + b3ateM8L3A + release/release + CjxoMj7KtdW9PC9oMj4KPGgzPrLp0a + 4 + b3ateM8L2gzPgo8cHJlIGNsYXNzPQ = "brush: SQL; "> select id, parent_id, name from a_district start with parent_id is null connect by id = parent_id order by id

Recursively query all upper-level nodes (including the current node) in PiXian County)

Select id, parent_id, name from a_district start with name = 'pianxia' connect by prior parent_id = id order by id

Recursively query all upper-level nodes (including the current node and root node) in PiXian County)

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

Recursively query all sub-towns and towns in PiXian (including the current node)

Select id, parent_id, name from a_district start with name = 'taobao' connect by prior id = parent_id order by id

Recursively query all towns and towns in Kaifeng City (including the 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 = 'kaifeng 'connect by prior id = parent_id order by id

Upstream recursive query of the depth of suburban towns and whether the depth contains leaf nodes

Select id, name, parent_id, level, connect_by_isleaf isleaf from a_district start with name = 'suburban country' connect by prior parent_id = id order by id

Downstream recursive query of Henan Province depth, whether it contains leaf nodes

Select id, name, parent_id, level, connect_by_isleaf isleaf from a_district start with name = 'henan province 'connect by prior id = parent_id order by id

Upstream recursive query of PiXian path

Select id, name, parent_id, substr (sys_connect_by_path (name, '->'), 3) name_path from a_district start with name = 'pianxia' connect by prior parent_id = id order by id

Downstream recursive query of Henan Province paths

Select id, name, parent_id, substr (sys_connect_by_path (name, '->'), 3) name_path from a_district start with name = 'henan province 'connect by prior id = parent_id order by id

Comprehensive use

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 = 'henan province 'connect by prior id = parent_id order by id