OCP课程20：SQL之分层查询

stonebox

课程目标：

• 分层查询概念
• 创建树形结构报表
• 格式化分层数据
• 修剪分支
  

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