Oracle笔记12——Oracle层次查询

转载

mb5ff2f2755a961 2021-10-29 15:00:00

层次查询（分级查询）

一般格式：

SELECT [LEVEL], 列名, 表达式 ... FROM 表格 [WHERE 条件] [START WITH 条件] [CONNECT BY PRIOR 条件];

--从顶向下查询
1.查询KING管理的所有的下级员工信息
SELECT * FROM emp START WITH ename = 'KING' CONNECT BY PRIOR 父列 = 子列;--empno父列   mgr子列
SELECT * FROM emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr;
SELECT * FROM emp START WITH ename = 'KING' CONNECT BY mgr = PRIOR empno;--同上

--从下向上查询
1.查询SMITH的所有的上级员工信息
SELECT * FROM emp START WITH ename = 'SMITH' CONNECT BY PRIOR 子列 = 父列;--empno父列   mgr 列  
SELECT * FROM emp START WITH ename = 'SMITH' CONNECT BY PRIOR mgr = empno;
SELECT * FROM emp START WITH ename = 'SMITH' CONNECT BY empno = PRIOR mgr;--同上

--使用LEVEL伪列
SELECT LEVEL,emp.* FROM emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr;
SELECT LEVEL,emp.* FROM emp START WITH ename = 'SMITH' CONNECT BY PRIOR mgr = empno;

--使用LEVEL，以及LPAD生成树状报告
SELECT LEVEL,ename, LPAD(ename, LENGTH(ename) + LEVEL*8, ' ') FROM emp START WITH empno = 7839 CONNECT BY PRIOR empno = mgr;

--排除某几行数据：使用WHERE添加限制条件
1.查询7839管理的所有下级员工信息，但是不包括SCOTT的信息
SELECT LEVEL, ename, LPAD(ename, LENGTH(ename) + LEVEL * 8, ' ')
  FROM emp WHERE ename <> 'SCOTT'
 START WITH empno = 7839
CONNECT BY PRIOR empno = mgr;
--使用CONNECT BY添加限制条件
2.查询7839管理的所有下级员工信息，但是不包括BLAKE以及BLAKE所有下属的信息
SELECT LEVEL, ename, LPAD(ename, LENGTH(ename) + LEVEL * 8, ' ')
  FROM emp
 START WITH empno = 7839
CONNECT BY PRIOR empno = mgr AND ename <> 'BLAKE';