[转] 层次化查询(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级，其孙子节点及其它的都没有 。