OCP課程20：SQL之分層查詢

課程目標：

• 分層查詢概念
• 建立樹形結構報表
• 格式化分層資料
• 修剪分支

 

 

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