课程目标：
- 分层查询概念
- 创建树形结构报表
- 格式化分层数据
- 修剪分支
1、分层查询概念
人员表里面有主键employee_id和外键manager_id，这种单个表中的行之间的层次关系可以构成一个树（tree），因而在行之间就存在父-子关系。通过树结构中的父-子关系，可以控制层次关系的开始点和方向（自顶向下/自底向上），分层查询就是用来解决这种父子关系问题的。
语法：
START WITH子句指定条件以及起始点
connect by prior指定层次的方向，如果prior后面的字段为父键，就是从上至下，如果prior后面的字段为子健，就是从下至上。
例子：从人员编号101开始，查找上级
SQL> select employee_id,last_name,job_id,manager_id from employees
2 start with employee_id=101
3 connect by prior manager_id=employee_id;
EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ------------------------- ---------- ----------
101 Kochhar AD_VP 100
100 King AD_PRES
例子：从人员名字King开始，查找下级
SQL> select last_name||' reports to '||prior last_name "Walk Top Down" from employees
2 start with last_name='King'
3 connect by prior employee_id=manager_id;
Walk Top Down
--------------------------------------------------------------
King reports to
King reports to
Kochhar reports to King
其中prior是一个伪列
2、格式化分层查询结果
可以使用level伪列来表示分层的级别，同时使用lpad函数格式化输出
例子：左填充来格式化分层查询的输出
SQL> select lpad(last_name,length(last_name)+(level*2)-2,'_') as org_chart
2 from employees
3 start with first_name='Steven' and last_name='King'
4 connect by prior employee_id=manager_id;
ORG_CHART
--------------------------------------------------------------------------------
King
__Kochhar
107 rows selected.
3、修剪分支
通过增加限定条件，对分层查询的分支进行修剪，有2种修剪方式：
- 使用where子句删除节点，但是保留该节点的下级
- 在connect by自今年增加条件，删除整个分支
例子：对上面的分层查询结果删除Higgins这个人员，但保留他的下级
SQL> select lpad(last_name,length(last_name)+(level*2)-2,'_') as org_chart from employees
2 where last_name<>'Higgins'
3 start with first_name='Steven' and last_name='King'
4 connect by prior employee_id=manager_id;
ORG_CHART
--------------------------------------------------------------------------------
King
106 rows selected.
例子：对上面的分层查询结果删除Higgins这个分支，包括Higgins这个节点
SQL> select lpad(last_name,length(last_name)+(level*2)-2,'_') as org_chart from employees
2 start with first_name='Steven' and last_name='King'
3 connect by prior employee_id=manager_id and last_name<>'Higgins';
ORG_CHART
--------------------------------------------------------------------------------
King
__Kochhar
105 rows selected.
例子：只显示第一个和第二个节点
SQL> select lpad(last_name,length(last_name)+(level*2)-2,'_') as org_chart from employees
2 start with first_name='Steven' and last_name='King'
3 connect by prior employee_id=manager_id and level<3;
ORG_CHART
--------------------------------------------------------------------------------
King
__Kochhar
__De Haan
4、相关习题：
（1）View the Exhibit and examine the structure of the EMPLOYEES table. You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree ?
A.WHERE
B.HAVING
C.GROUP BY
D.START WITH
E.CONNECT BY PRIOR
答案：E
（2）Which statements are true regarding the hierarchical query in Oracle Database 10g(Choose all that apply.)
A.It is possible to retrieve data only in top?down hierarchy.
B.It is possible to retrieve data in top?down or bottom?up hierarchy.
C.It is possible to remove an entire branch from the output of the hierarchical query.
D.You cannot specify conditions when you retrieve data by using a hierarchical query.
答案：BC
（3）View the Exhibit and examine the structure of the EMPLOYEES table. Evaluate the following SQL statement: SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id=manager_id? Which statement is true regarding the output for this command?
A.It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by his or her peers.
B.It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by the employee to whom he or she reports.
C.It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by employees below him or her in the hierarchy.
D.It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is101, followed by employees up to one level below him or her in the hierarchy.
答案：C
（4）View the Exhibit and examine the details of the EMPLOYEES table. You want to generate a hierarchical report for all the employees who report to the employee whose EMPLOYEE_ID is 100. Which SQL clauses would you require to accomplish the task? (Choose all that apply.)
A.WHERE
B.HAVING
C.GROUP BY
D.START WITH
E.CONNECT BY
答案：ADE
（5）View the Exhibit and examine the description of the EMPLOYEES table. Evaluate the following SQL statement: SELECT employee_id, last_name, job_id, manager_id, LEVEL FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id=manager_id；Which two statements are true regarding the output of this command？(Choose two.)
A.The output would be in top-down hierarchy starting with EMPLOYEE_ID having value 101.
B.The output would be in bottom-up hierarchy starting with EMPLOYEE_ID having value 101.
C.The LEVEL column displays the number of employees in the hierarchy under the employee having the EMPLOYEE_ID 101.
D.The LEVEL column displays the level in the hierarchy at which the employee is placed under theemployee having the EMPLOYEE_ID 101.
答案：AD
（6）View the Exhibit and examine the details of the EMPLOYEES table. Evaluate the following SQL statements: Statement 1: SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id =101 CONNECT BY PRIOR employee_id = manager_id AND manager_id != 108；Statement 2: SELECT employee_id, last_name, job_id, manager_id FROM employees WHERE manager_id != 108 START WITH employee_id=101 CONNECT BY PRIOR employee_id = manager_id ； Which two statements are true regarding the above SQL statements ? (Choose two.)
A.Statement 2 would not execute because the WHERE clause condition is not allowed in a statement that has the START WITH clause.
B.The output for statement 1 would display the employee with MANAGER_ID 108 and all the employees below him or her in the hierarchy.
C.The output of statement 1 would neither display the employee with MANAGER_ID 108 nor any employee below him or her in the hierarchy.
D.The output for statement 2 would not display the employee with MANAGER_ID 108 but it would display all the employees below him or her in the hierarchy.
答案：CD