层次化查询（start with ... connect by prior）

这里将介绍层次化查询 start with ... connect by prior

语法：

SELECT [LEVEL], column,expression,... FROM table WHERE [WHERE where_cause]

[[START WITH start_condition] [CONNECT BY PRIOR prior_conditon]]

LEVEL:伪列，代表位于树的第几层。对根节点来说，LEVEL返回1，根节点返回2，依次类推。

start_condition：定义层次化的起点。层次化查询必须指定START WITH。

prior_conditon：定义了父行与子行之间的关系。层次化查询必须指定CONNECT BY PRIOR。



CREATE TABLE more_employees (
employee_id INTEGER
CONSTRAINT more_employees_pk PRIMARY KEY,
manager_id INTEGER
CONSTRAINT more_empl_fk_fk_more_empl
REFERENCES more_employees(employee_id),
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
title VARCHAR2(20),
salary NUMBER(6, 0)
);

INSERT INTO more_employees ( employee_id, manager_id, first_name, last_name, title, salary) VALUES ( 1, NULL, 'James', 'Smith', 'CEO', 800000);
INSERT INTO more_employees ( employee_id, manager_id, first_name, last_name, title, salary) VALUES ( 2, 1, 'Ron', 'Johnson', 'Sales Manager', 600000);
INSERT INTO more_employees ( employee_id, manager_id, first_name, last_name, title, salary) VALUES ( 3, 2, 'Fred', 'Hobbs', 'Sales Person', 200000);
INSERT INTO more_employees ( employee_id, manager_id, first_name, last_name, title, salary) VALUES ( 4, 1, 'Susan', 'Jones', 'Support Manager', 500000);
INSERT INTO more_employees ( employee_id, manager_id, first_name, last_name, title, salary) VALUES ( 5, 2, 'Rob', 'Green', 'Sales Person', 40000);
INSERT INTO more_employees ( employee_id, manager_id, first_name, last_name, title, salary) VALUES ( 6, 4, 'Jane', 'Brown', 'Support Person', 45000);
INSERT INTO more_employees ( employee_id, manager_id, first_name, last_name, title, salary) VALUES ( 7, 4, 'John', 'Grey', 'Support Manager', 30000);
INSERT INTO more_employees ( employee_id, manager_id, first_name, last_name, title, salary) VALUES ( 8, 7, 'Jean', 'Blue', 'Support Person', 29000);
INSERT INTO more_employees ( employee_id, manager_id, first_name, last_name, title, salary) VALUES ( 9, 6, 'Henry', 'Heyson', 'Support Person', 30000);
INSERT INTO more_employees ( employee_id, manager_id, first_name, last_name, title, salary) VALUES ( 10, 1, 'Kevin', 'Black', 'Ops Manager', 100000);
INSERT INTO more_employees ( employee_id, manager_id, first_name, last_name, title, salary) VALUES ( 11, 10, 'Keith', 'Long', 'Ops Person', 50000);
INSERT INTO more_employees ( employee_id, manager_id, first_name, last_name, title, salary) VALUES ( 12, 10, 'Frank', 'Howard', 'Ops Person', 45000);
INSERT INTO more_employees ( employee_id, manager_id, first_name, last_name, title, salary) VALUES ( 13, 10, 'Doreen', 'Penn', 'Ops Person', 47000);
commit ;



1、使用伪列LEVEL

select level,employee_id ,manager_id ,first_name ,last_name
from more_employees
start with employee_id = 1
connect by prior employee_id = manager_id
order by level ;

LEVEL EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
------------ ------------ ------------ ---------- ----------
1 1 James Smith
2 10 1 Kevin Black
2 2 1 Ron Johnson
2 4 1 Susan Jones
3 13 10 Doreen Penn
3 7 4 John Grey
3 11 10 Keith Long
3 5 2 Rob Green
3 3 2 Fred Hobbs
3 12 10 Frank Howard
3 6 4 Jane Brown
4 8 7 Jean Blue
4 9 6 Henry Heyson

13 rows selected.

2、格式化层次查询

select level,
lpad(' ',10*level - 10) || first_name || ' ' || last_name,
employee_id ,
manager_id
from more_employees
start with employee_id = 1
connect by prior employee_id = manager_id ;

3、从非根节点开始遍历（start with 限制）


select level,
lpad(' ',10*level - 10) || first_name || ' ' || last_name,
employee_id ,
manager_id
from more_employees
start with last_name = 'Jones'
connect by prior employee_id = manager_id ;

4、在START WITHE子句中使用子查询

select level,
lpad(' ',10*level - 10) || first_name || ' ' || last_name,
employee_id ,
manager_id
from more_employees
start with employee_id =
(select employee_id from more_employees where first_name = 'Kevin' and last_name = 'Black')
connect by prior employee_id = manager_id ;

5、从下往上遍历

可以从某个子节点开始，自下而上进行遍历

select level,
lpad(' ',10*level - 10) || first_name || ' ' || last_name,
employee_id ,
manager_id
from more_employees
start with employee_id = 9
connect by prior manager_id = employee_id ;

6、从层次查询中删除节点和分支

只过滤某些行。用where限制

select level,
lpad(' ',10*level - 10) || first_name || ' ' || last_name,
employee_id ,
manager_id
from more_employees
where last_name <> 'Johnson'
start with employee_id = 1
connect by prior employee_id = manager_id ;

过滤父节点下的所有子节点。用connect by prior ... and

select level,
lpad(' ',10*level - 10) || first_name || ' ' || last_name,
employee_id ,
manager_id
from more_employees
start with employee_id = 1
connect by prior employee_id = manager_id and last_name <> 'Johnson' ;



注意：

如果 start with 后面是子节点: connect by prior 后面要是 子节点 = 父节点,则是典型的父子结构 ；connect by prior 后面要是 父节点 = 子节点, 将是 子父结构（倒序）。

如果 start with 后面是父节点：后面要是 子节点 = 父节点，将是正序 ；connect by prior 后面要是 父节点 = 子节点 ，将是 子父结构（倒序）,并且数据只有 父节点 和 子节点 2级，其孙子节点及其它的都没有 。