简单的树形查询--prior放在select中使用

简单的树形查询

使用scott用户下的表：emp

想要查出上级主管的姓名(sql1)：

select e.empno as "员工编码",
       e.ename as "员工姓名",
       e.mgr as "主管编码",
       "主管姓名" as "主管姓名"
from emp e 
start with e.empno = 7566
connect by (prior e.empno) = e.mgr
order by 1;

查询之后主管姓名要如何展示出来呢，想想可以再关联一次emp查出姓名代码入戏(sql2)：

select e.empno as "员工编码",
       e.ename as "员工姓名",
       e.mgr as "主管编码",
       b.ename  as "主管姓名"
from emp e left join emp b on e.mgr = b.empno
start with e.empno = 7566
connect by (prior e.empno) = e.mgr
order by 1;

看看执行计划：

 Plan Hash Value  : 1231760462 
--------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name | Rows | Bytes | Cost |