• 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