重庆思庄Oracle、Redhat认证学习论坛

标题: 【课堂笔记】【SQL】18.Hierarchical Retrieval [打印本页]

作者: 5dm 时间: 2012-11-6 10:36
标题: 【课堂笔记】【SQL】18.Hierarchical Retrieval

解决树状关系查询

示例：

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去掉整个枝干。

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)