第29章 层次查询

第29章 层次查询

查询员工上下级关系

查看员工上级领导
select empno,ename,mgr from emp start with empno=7839 connect by prior mgr=empno;

查看领导的下属
select empno,ename,mgr from emp start with empno=7839 connect by prior empno=mgr;

分级查看，加入位列level
select empno,ename,mgr,level from emp start with empno=7839 connect by prior mgr=empno;

select empno,ename,mgr,level from emp start with empno=7839 connect by prior empno=mgr;

层次关系美化

第一种方法

select lpad(ename,(length(ename)+level*2)-2,' ') ename from emp start with empno=7839 connect by prior empno=mgr;

SQL> select lpad(ename,(length(ename)+level*2)-2,' ') ename from emp start with empno=7839 connect by prior empno=mgr;

ENAME
--------------------------------------------------------------------------------
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER

ENAME
--------------------------------------------------------------------------------
JAMES
CLARK
MILLER

14 rows selected.

第二种方法，使用系统函数sys_connect_by_path(colname,' ')查看

select sys_connect_by_path(ename,'/') ename from emp start with empno=7839 connect by prior empno=mgr;

SQL> select sys_connect_by_path(ename,'/') ename from emp start with empno=7839 connect by prior empno=mgr;

ENAME
--------------------------------------------------------------------------------
/KING
/KING/JONES
/KING/JONES/SCOTT
/KING/JONES/SCOTT/ADAMS
/KING/JONES/FORD
/KING/JONES/FORD/SMITH
/KING/BLAKE
/KING/BLAKE/ALLEN
/KING/BLAKE/WARD
/KING/BLAKE/MARTIN
/KING/BLAKE/TURNER

ENAME
--------------------------------------------------------------------------------
/KING/BLAKE/JAMES
/KING/CLARK
/KING/CLARK/MILLER

14 rows selected.