sql遞迴查詢子級

夜色狼發表於2015-10-13

WITH T(emp_no, name, dept_no, the_level, path,path1,manager_id)
AS(
SELECT emp_no, name, dept_no
,1 AS the_level
,'\'||name path
,'\'||manager_id path1
,manager_id
FROM BASE_HR_EMP where emp_no='10002241'
UNION ALL
SELECT e.emp_no, e.name, e.dept_no
,1 + t.the_level
,t.path||'\'||e.name path
,t.path1||'\'||e.manager_id path
,t.manager_id
FROM T t, BASE_HR_EMP e
WHERE t.emp_no = e.manager_id
)
SELECT * FROM T

相關文章