1.分级查询
select [level],column,expr...
from table
[where condition(s)]
[start with condition(s)]
[connect by prior condition(s)];
level:对每行返回一个级别,对根行level伪列返回1;对孩子的level伪列返回2,以此类推
start with:指定层次的根行
connct by prior:指定查询的方向
1)start with子句可以和任何有效的条件协同使用
start with column1=value
2)使用emp表,从名字是king的雇员开始
...start with ename='king'
...start with mgr is null
3)一个start with条件可以包含子查询
...start with empno=(select empno
from emp
where ename='king')
connect by prior column1=column2
4)使用emp表从顶向下遍历:
...connect by prior empno=mgr
5)使用emp表从底向上遍历:
...connect by empno=prior mgr
2.遍历树:
1)自顶向下遍历
例:
select level,empno,ename||' 雇员的经理是 '||
prior ename "雇佣关系"
from emp
start with ename='king'
connect by prior empno=mgr;
结果:
level empno 雇佣关系
1 7839 king 雇员的经理是
2 7566 jones 雇员的经理是 king
3 7788 scott 雇员的经理是 jones
4 7876 adams 雇员的经理是 scott
3 7902 ford 雇员的经理是 jones
4 7369 smith 雇员的经理是 ford
2 7698 blake 雇员的经理是 king
3 7499 allen 雇员的经理是 blake
3 7521 ward 雇员的经理是 blake
3 7654 martin 雇员的经理是 blake
3 7844 turner 雇员的经理是 blake
3 7900 james 雇员的经理是 blake
2 7782 clark 雇员的经理是 king
3 7934 miller 雇员的经理是 clark
2)从底向上遍历
例:
select level,empno,ename,job,mgr
from emp
start with empno=7876
connect by empno=prior mgr;
结果:
level empno ename job mgr
1 7876 adams clerk 7788
2 7788 scott analyst 7566
3 7566 jones manager 7839
4 7839 king president
3.用level和lpad格式化分级报告
创建一个报告,显示公司的管理层,从最高级别开始,缩进下面跟随的级别
例:
select level,lpad(ename,length(ename)+(level*2)-2,'-') as org_chart
from emp
start with ename='king'
connect by prior empno=mgr;
结果:
level org_chart
1 king
2 --jones
3 ----scott
4 ------adams
3 ----ford
4 ------smith
2 --blake
3 ----allen
3 ----ward
3 ----martin
3 ----turner
3 ----james
2 --clark
3 ----miller
4.修剪分支
1)用where子句去除一个结点
where ename!='scott'
2)用connect by子句去除一个分支
connect by prior
empno=mgr
and ename!='scott'