层次查询
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;
