层次查询
select * from emp;
select empno,
ename,
job,
mgr,
sal,
deptno,level lv,
sys_connect_by_path(ename, '->') tree_path,
connect_by_root(ename) tree_root,
connect_by_isleaf isleaf,
decode(connect_by_isleaf,1,ename,null) tree_leaf
from emp
start with empno = 7369
connect by prior mgr = empno
order by level,empno;
with t(empno,
ename,
job,
mgr,
sal,
deptno,
lv,
tree_path,
tree_root,
isleaf,tree_leaf) as
(select empno,
ename,
job,
mgr,
sal,
deptno,
1 lv,
'->'||ename,
ename,
(select decode(count(1), 0, 1) from emp where a.mgr = empno) isleaf,
(select decode(count(1), 0, a.ename) from emp where a.mgr = empno) leafname
from emp a
where empno = '7369'
union all
select a.empno,
a.ename,
a.job,
a.mgr,
a.sal,
a.deptno,
b.lv + 1,
b.tree_path || '->' || a.ename,
b.tree_root,
(select decode(count(1), 0, 1) from emp where a.mgr = empno) isleaf,
(select decode(count(1), 0,a.ename) from emp where a.mgr = empno) leafname
from emp a, t b
where a.empno = b.mgr)
select * from t order by lv,empno;
