- 1. 分级查询
- 2. 目标通过本章学习，您将可以:
分级查询的概念
创建树形的报表
格式划分级数据
在树形结构中删除分支和节点
- 3. EMPLOYEES 表中的数据
- 4. 树形结构De HannKingHunoldEMPLOYEE_ID = 100 (双亲)MANAGER_ID = 100 (孩子)WhalenKochharHigginsMourgosZlotkeyRajsDaviesMatosGietzErnstLorentzHartsteinFay AbelTaylorGrantVargas
- 5. 分级查询WHERE 条件:expr comparison_operator exprSELECT [LEVEL], column, expr...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)] ;
- 6. 遍历树始 点指定需要满足的条件
接受有效的条件
遍历 EMPLOYEES 表, 以姓名为 Kochhar的员工作为始点...START WITH last_name = 'Kochhar'START WITH column1 = value
- 7. 遍历树方向从顶到底 Column1 = Parent Key
Column2 = Child Key从底到顶 Column1 = Child Key
Column2 = Parent Key从顶到底遍历 EMPLOYEES 表CONNECT BY PRIOR column1 = column2... CONNECT BY PRIOR employee_id = manager_id
- 8. 遍历树: 从底到顶SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id ;
- 9. 遍历树: 从顶到底SELECT last_name||' reports to '||
PRIOR last_name "Walk Top Down"
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id ;…
- 10. 使用 LEVEL 伪列标记层次 De HannKingHunoldWhalenKochharHigginsMourgosZlotkeyRajsDaviesMatosGietzErnstLorentzHartsteinFay AbelTaylorGrantVargas层次1
根/双亲层次2
双亲/孩子层次3
双亲/孩子
/叶子层次4
叶子
- 11. 使用 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
- 12. 修剪树枝使用 WHERE 子句删除节点使用CONNECT BY 子句删除树枝WHERE last_name != 'Higgins'CONNECT BY PRIOR
employee_id = manager_id
AND last_name != 'Higgins'KochharHigginsGietzWhalenKochharHigginsWhalenGietz
- 13. 总结通过本章学习,您已经可以:
对具有层次关系的数据创建树形报表
指定遍历的始点和方向
删除节点和树枝
- 14. Hidden Slide
- 15. Hidden Slide
- 16. Hidden Slide
