【课堂笔记】【SQL】18.Hierarchical Retrieval

5dm

解决树状关系查询

示例：

QL> 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

SQL> select last_name || 'report to' || prior last_name "Walk T D"
  2  from employees
  3  start with last_name='King'
  4  connect by prior employee_id=manager_id;

Walk T D
-----------------------------------------------------------
Kingreport to
Kingreport to
Kochharreport toKing
Greenbergreport toKochhar
Favietreport toGreenberg
Chenreport toGreenberg
Sciarrareport toGreenberg
Urmanreport toGreenberg
Poppreport toGreenberg
Whalenreport toKochhar
Mavrisreport toKochhar

Walk T D
-----------------------------------------------------------
Baerreport toKochhar
Higginsreport toKochhar

 

LEVEL和LPAD

示例：

SQL> select lpad(last_name,length(last_name)+(level*2)-2,'#')
  2  as org_chart
  3  from employees
  4  start with first_name='Steven' and last_name='King'
  5  connect by prior employee_id=manager_id;

ORG_CHART
--------------------------------------------------------------------------------
King
##Kochhar
####Greenberg
######Faviet
######Chen
######Sciarra
######Urman
######Popp
####Whalen
####Mavris
####Baer

 

 

Pruning Branches修剪问题

WHERE去掉一个节点，AND去掉整个枝干。