层次化查询

层次化查询

--从上往下遍历

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

--使用伪列，显示节点在树中的层次

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

--获取树的层次数

select count(distinct level) from emp start with empno=7839 connect by prior empno = mgr

--格式化层次化查询

select level,lpad(' ',2 * level-1) || ename as emplyee from emp start with empno=7839 connect by prior empno = mgr

--从非根节点开始遍历

select level,lpad(' ',2 * level-1) || ename as emplyee from emp start with ename='JONES' connect by prior empno = mgr

--在start with子句使用子查询

select level,lpad(' ',2 * level-1) || ename as emplyee from emp start with empno=(select empno from emp where ename='BLAKE') connect by prior empno =   mgr

--从下往上遍历

select level,lpad(' ',2 * level-1) || ename as emplyee from emp start with ename='SMITH' connect by prior mgr = empno

--从层次查询中移除特定的节点(移除ename=BLAKE的节点)

select level,lpad(' ',2 * level-1) || ename as emplyee from emp where ename != 'BLAKE' start with empno=7839 connect by prior empno =   mgr

--从层次查询中移除特定的分支(移除ename=BLAKE的分支)

select level,lpad(' ',2 * level-1) || ename as emplyee from emp start with empno=7839 connect by prior empno = mgr and ename != 'BLAKE'

--在层次查询中加入其他的查询条件

select level,lpad(' ',2 * level-1) || ename as emplyee,sal from emp where sal >=2000 start with empno=7839 connect by prior empno =   mgr