sql之22 Hierarchical Retrieval

使用分級獲取，生成樹狀報表(use hierarchical queries to create tree-structured reports)[@more@]

--語法
SELECT [LEVEL], column, expr...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)];
WHERE condition: expr comparison_operator expr

--第一種方式：Walking the Tree: From the Bottom Up
SQL> select employee_id,last_name,job_id,manager_id
2 from employees
3 start with employee_id=101
4 connect by prior manager_id=employee_id;

EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ---------- ---------- ----------
101 Kochhar AD_VP 100
100 King AD_PRES

--第二種方式：Walking the Tree: From the Top Down
SQL> ;
1 select employee_id,last_name,job_id,manager_id
2 from employees
3 start with employee_id=101
4* connect by prior employee_id=manager_id
SQL> /

EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ---------- ---------- ----------
101 Kochhar AD_VP 100
200 Whalen AD_ASST 101
205 Higgins AC_MGR 101
206 Gietz AC_ACCOUNT 205

-- 理解prior修飾符
SQL> select last_name||' report to '|| prior last_name "Walk Top Down"
2 from employees
3 start with last_name='King'
4 connect by prior employee_id=manager_id;

Walk Top Down
--------------------------------------------------------------
King report to
Kochhar report to King
Whalen report to Kochhar
Higgins report to Kochhar
Gietz report to Higgins
De Haan report to King
Hunold report to De Haan
Ernst report to Hunold

--如何格式化樹狀分級報告？level是個偽列(pseudocolumn)
--Formatting Hierarchical Reports Using LEVEL and LPAD
SQL> column org_chart format a20
SQL> select lpad(last_name,length(last_name)+(level*2)-2,'.')
2 as org_chart
3 from employees
4 start with last_name='King'
5 connect by prior employee_id=manager_id

ORG_CHART
-------------------
King
..Kochhar
....Whalen
....Higgins
......Gietz

-- 如何修剪枝杈？Pruning Branches
SELECT department_id, employee_id,last_name, job_id, salar y
FROM employees
WHERE last_name != 'Higgins'
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
-- or
SELECT department_id, employee_id,last_name, job_id, salary
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
AND last_name != 'Higgins';

-- 測試題：Create a report that shows the hierarchy of the managers for the employee Lorentz. Display his immediate manager first.
SQL> select last_name
2 from employees
3 start with employee_id=(select b.manager_id from employees b where b.last_name='Lorentz')
4 connect by prior manager_id=employee_id
5 and last_name != 'Lorentz'
LAST_NAME
----------
Hunold
De Haan
King

-- test 2: Create an indented report showing the management hierarchy starting from the employee whose LAST_NAME is Kochhar. Print the employee’s last name, manager ID, and department ID. Give alias names to the columns as shown in the sample output.
SQL> col name for a10
SQL> select lpad(last_name,length(last_name)+(level*2)-2,'_') name,
2 manager_id mgr,department_id deptno
3 from employees
4 start with last_name='Kochhar'
5 connect by prior employee_id=manager_id;