1、分級(jí)查詢,目標(biāo),通過(guò)本章學(xué)習(xí)，您將可以: 分級(jí)查詢的概念 創(chuàng)建樹(shù)形的報(bào)表 格式劃分級(jí)數(shù)據(jù) 在樹(shù)形結(jié)構(gòu)中刪除分支和節(jié)點(diǎn),EMPLOYEES 表中的數(shù)據(jù),樹(shù)形結(jié)構(gòu),De Hann,King,Hunold,EMPLOYEE_ID = 100 (雙親),MANAGER_ID = 100 (孩子),Whalen,Kochhar,Higgins,Mourgos,Zlotkey,Rajs,Davies,Matos,Gietz,Ernst,Lorentz,Hartstein,Fay,Abel,Taylor,Grant,Vargas,分級(jí)查詢,WHERE 條件:,expr comparison_operator

2、 expr,SELECT LEVEL, column, expr. FROM table WHERE condition(s) START WITH condition(s) CONNECT BY PRIOR condition(s) ;,遍歷樹(shù),始 點(diǎn),指定需要滿足的條件 接受有效的條件 遍歷 EMPLOYEES 表, 以姓名為 Kochhar的員工作為始點(diǎn),.START WITH last_name = Kochhar,START WITH column1 = value,遍歷樹(shù),方向,從頂?shù)降?Column1 = Parent Key Column2 = Child Key,從底到頂,C

3、olumn1 = Child Key Column2 = Parent Key,從頂?shù)降妆闅v EMPLOYEES 表,CONNECT BY PRIOR column1 = column2,. CONNECT BY PRIOR employee_id = manager_id,遍歷樹(shù): 從底到頂,SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR manager_id = employee_id ;,遍歷樹(shù): 從頂?shù)降?SELE

4、CT last_name| reports to | PRIOR last_name Walk Top Down FROM employees START WITH last_name = King CONNECT BY PRIOR employee_id = manager_id ;,使用 LEVEL 偽列標(biāo)記層次,De Hann,King,Hunold,Whalen,Kochhar,Higgins,Mourgos,Zlotkey,Rajs,Davies,Matos,Gietz,Ernst,Lorentz,Hartstein,Fay,Abel,Taylor,Grant,Vargas,層次1

5、根/雙親,層次2 雙親/孩子,層次3 雙親/孩子 /葉子,層次4 葉子,使用 LEVEL 和 LPAD格式化分層查詢,COLUMN org_chart FORMAT A12 SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,_) AS org_chart FROM employees START WITH last_name=King CONNECT BY PRIOR employee_id=manager_id,修剪樹(shù)枝,使用 WHERE 子句刪除節(jié)點(diǎn),使用CONNECT BY 子句刪除樹(shù)枝,WHERE last_name != Higgins,CONNECT BY PRIOR employee_id = manager_id AND last_name != Higgins,Kochhar,Higgins,Gietz,Whalen,Kochhar,Higgins,Whalen,Gietz,總結(jié),通過(guò)本