层次查询(分级查询)
SELECT [LEVEL], column, expr...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)] ;
–LEVEL:节点的层次,伪列,由查询的起点开始算起为1,依次类推。
–FROM table:指定表、视图或包含列的快照,你只能从单独的一个表中选
择。
–WHERE: 限制返回的行。
–Condition:是一个比较式。
–START WITH:指定层次的根行 (起点)。这个子句对于一个正确的分级查询
是必须的。
–CONNECT BY PRIOR:指定存在父与子行的关系列。对于分级查询该子句是
必须的。
起点
• 指定必须满足的条件
• 接受有效的条件
START WITH column1 = value
使用 EMP 表,从名字是KING 的雇员开始
...START WITH ename = ‘KING'
CONNECT BY PRIOR column1 = column2
从顶向下遍历,用 EMP 表
... CONNECT BY PRIOR empno = mgr
方向
从顶向下 Column1 = Parent Key
Column2 = Child Key
从底向上 Column1 = Child Key
Column2 = Parent Key
-----从顶向下
SELECT LEVEL ,empno,ename,mgr
FROM emp
START WITH empno =7839
CONNECT BY PRIOR empno=mgr
---------从底向上
SELECT LEVEL ,empno,ename,mgr
FROM emp
START WITH empno =7839
CONNECT BY PRIOR mgr=empno
用 LEVEL和LPAD生成分级报告
创建一个报告显示公司的管理层,从最高
级别开始,缩进下面跟随的级别
SELECT LPAD(ename, LENGTH( ename )+(LEVEL*2)-2,'*')
AS org_chart
FROM emp
START WITH ename='KING'
CONNECT BY PRIOR empno=mgr
修剪分支
用 WHERE 子句 用 CONNECT BY 子句
去除一个结点 去除一个分支
WHERE ename <>'Higgins' CONNECT BY PRIOR
empno = mgr
Kochhar AND ename <> 'Higgins'
Kochhar
Whalen Higgins
Whalen Higgins
Gietz
Gietz
课后作业
1.产生一个报告显示 BLAKE的所有下级(包括直接和间接下级)雇员的名字、薪水和部号。
SELECT ename ,sal ,deptno
FROM emp
START WITH ename='BLAKE'
CONNECT BY PRIOR empno =mgr
2.创建一个报告显示对于雇员 SMITH 经理的层次,包括级别和姓名,首先显示他的直接经理。
SELECT LEVEL,ename
FROM emp
WHERE ename<>'SMITH'
START WITH ename='SMITH'
CONNECT BY PRIOR mgr =empno
3.创建一个缩进报告显示经理层次,从名字为 KING的雇员开始,显示雇员的名字、经理ID和部门ID。
SELECT ename,mgr,deptno,lpad (ename,length(ename)+(LEVEL-1)*2,'*')
FROM emp
START WITH ename ='KING'
CONNECT BY PRIOR empno =mgr
4.产生一个公司组织图表显示经理层次。从最顶级的人开始,排除所有job为CLERK的人,还要排除FORD和那些对FORD报告的雇员。
SELECT ename,mgr,deptno,lpad (ename,length(ename)+(LEVEL-1)*2,'*')
FROM emp
WHERE job<>'CLERK'
START WITH ename ='KING'
CONNECT BY PRIOR empno =mgr AND ename<>'FORD'