28.层次查询

遍历树自底到顶

hr@ORCLPDB01 2023-02-26 20:31:01> 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

Elapsed: 00:00:00.01

遍历树自顶到底

hr@ORCLPDB01 2023-02-26 20:34:35> select last_name||' reports to '||
  2  prior last_name "Walk top Down"
  3  from employees
  4  start with last_name = 'King'
  5  connect by prior employee_id = manager_id;

Walk top Down
--------------------------------------------------------------
King reports to
King reports to
Kochhar reports to King
Greenberg reports to Kochhar
Faviet reports to Greenberg
Chen reports to Greenberg

使用level和lpad美化层次报告

hr@ORCLPDB01 2023-02-26 20:40:40> r
  1  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
____Higgins
______Gietz
__De Haan
____Hunold
______Ernst
______Austin
______Pataballa
______Lorentz
__Raphaely
____Khoo
____Baida
____Tobias
____Himuro
____Colmenares
__Weiss
____Nayer
____Mikkilineni
____Landry
____Markle
____Taylor
____Fleaur
____Sullivan
____Geoni
__Fripp
____Bissot
____Atkinson
____Marlow
____Olson
____Sarchand
____Bull
____Dellinger
____Cabrio
__Kaufling
____Mallin
____Rogers
____Gee
____Philtanker
____Chung
____Dilly
____Gates
____Perkins
__Vollman
____Ladwig
____Stiles
____Seo
____Patel
____Bell
____Everett
____McCain
____Jones
__Mourgos
____Rajs
____Davies
____Matos
____Vargas
____Walsh
____Feeney
____OConnell
____Grant
__Russell
____Tucker
____Bernstein
____Hall
____Olsen
____Cambrault
____Tuvault
__Partners
____King
____Sully
____McEwen
____Smith
____Doran
____Sewall
__Errazuriz
____Vishney
____Greene
____Marvins
____Lee
____Ande
____Banda
__Cambrault
____Ozer
____Bloom
____Fox
____Smith
____Bates
____Kumar
__Zlotkey
____Abel
____Hutton
____Taylor
____Livingston
____Grant
____Johnson
__Hartstein
____Fay