遍历树自底到顶
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标签：__,______,层次,King,28,查询,____,id,name From： https://www.cnblogs.com/yuanzijian/p/17157614.html