Oracle-4-2自连接、层次查询和分析函数

2013年10月25日
Oracle 10g SQL使用
9
层次搜索从顶到底的搜索：
– 例1：EMP 表每一条记录都有一个唯一标识当前雇员的 empno和标识这个雇员的经理的mgr列。如果mgr 为空表示 该雇员是该机构最顶级：
select LPAD(' ',4*(LEVEL-1)) ||ename name ,empno,mgr, LEVEL from emp start with mgr is null connect by prior empno=mgr;
2013年10月25日
Oracle 10g SQL使用
5
层次检索(查询)
SELECT [LEVEL], column, expr... FROM table [WHERE condition(s)] [START WITH condition(s)] [CONNECT BY PRIOR condition(s)]
2013年10月25日
Oracle 10g SQL使用
13
层次查询样例 例子3-CONNECT BY PRIOR：
– 如果mgr 为空，则该雇员是该机构的最顶级。现在要列 出每个雇员的层次结构(从顶到底)：
SQL> select lpad(' ',4*(LEVEL-1)) ||ename name ,empno,mgr, LEVEL 2 from emp start with mgr is null 3 connect by prior empno=mgr; NAME EMPNO MGR LEVEL -------------------- ---------- ---------- ---------KING 7839 1 JONES 7566 7839 2 SCOTT 7788 7566 3 ADAMS 7876 7788 4 FORD 7902 7566 3 SMITH 7369 7902 4 BLAKE 7698 7839 2 ALLEN 7499 7698 3 WARD 7521 7698 3 MARTIN 7654 7698 3 TURNER 7844 7698 3 JAMES 7900 7698 3 CLARK 7782 7839 2 MILLER 7934 7782 3
2013年10月25日
14 rows selected.
Oracle 10g SQL使用
10
层次搜索-遍历方向 从底到顶的搜索(遍历至树根 )：
– 例2：现在要从某个雇员往他的上级列出该雇员的层次结构(
从底到顶)：
SQL> col ename for a30 SQL> select lpad(' ',4*(level-1))||ename ename,mgr,empno from emp start with mgr=7788 connect by prior mgr= empno ; ENAME MGR EMPNO ---------------------------------ADAMS 7788 7876 SCOTT 7566 7788 JONES 7839 7566 KING 783 10g SQL使用
8
层次搜索-遍历方向 连接条件：
– CONNECT BY PRIOR column1 = column2 – 从顶到底(topdown)，方法是： • Top down => Column1 = Parent Key • Column2 = Child Key • 对于emp表来说是：... CONNECT BY PRIOR employee_id = manager_id (见例子1) – 从底到顶 (BottomUp)，方法是： • Bottom up =>Column1 = Child Key • Column2 = Parent Key
层次查询和分析函数
EMP PK EMPNO ENAME JOB DEPTNO=DEPTNO MGR HIREDATE SAL COMM DEPTNO DEPT PK DEPTNO DNAME LOC
FK2
FK1
EMPNO=MGR
查询emp表的样例 SCOTT模式下的emp表：
SELECT empno,ename,mgr FROM emp WHERE mgr IN(SELECT empno FROM emp WHERE mgr IS NULL); EMPNO ENAME MGR ---------- ---------- ---------7566 JONES 7839 7698 BLAKE 7839 7782 CLARK 7839 SELECT empno,ename,mgr FROM emp WHERE mgr=7566; EMPNO ENAME MGR ---------- ---------- ---------7788 SCOTT 7566 7902 FORD 7566 SELECT empno,ename,mgr FROM emp WHERE mgr=7782; EMPNO ENAME MGR ---------- ---------- ---------7934 MILLER 7782
2013年10月25日
Oracle 10g SQL使用
7
层次检索-编历树
树根的行由START WITH子句确定，它可用作任何有效性条件连接； 比如EMP表中用start with KING(因为他是公司总经理)，如： START WITH mgr IS NULL EMP表,可用start with emp_id Kochhar做为条件，如：…START WITH emp_id=(SELECT emp_id FROM emp Where last_name=‘Kochhar’ ) 如果省略START WITH 子句，表示从树根搜索所有行。如果有一个Where 子 句被使用，则从该指定的行搜索所有行，这不会反应真正的层次。 CONNECT BY PRIOR和START WITH 不是ANSI（美国国家标准局 ）标准.
SELECT 是一个标准的 SELECT 语句 LEVEL 是层次查询所返回的值：1 为根(root) ；2 表示根的分枝(孩 子),等等 FROM 后可以是表、视图、快照，只选择一个 WHERE 限制行的返回；condition 表达式 START WITH指定从根的何处开始搜索
2013年10月25日
Oracle 10g SQL使用
16
层次查询样例
例子5-从根开始，从顶到底，消除Higgins， 及子行：
SQL> 2 3 4 5 SELECT deptno, empno,ename, job , sal FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr AND ename != 'Higgins'; JOB SAL --------- ---------PRESIDENT 5000 MANAGER 2975 ANALYST 3000 CLERK 1100 ANALYST 3000 CLERK 800 MANAGER 2850 SALESMAN 1600 SALESMAN 1250 SALESMAN 1250 SALESMAN 1500 CLERK 950 MANAGER 2450 CLERK 1300
自然的树结构（EMP表）
emp表本身的数据存在树型关系:
emp表的树型结构是按照empno和mgr的关系而建立 这样的关系可在开发时的自我连接时使用
mgr行包含有他所雇佣的员工的编号 这样的父子关系使你可以控制:
直接的搜索 从层次的某个点开始
emp表 在该表中，你可查询出表中行与行之间的关系 关系数据库并不将这些关系存储在数据库里，然 而表里可存在树型走向问题(tree walking) 而层次查询是按照树型分枝查询的一种方法 可想象与一个家族树一样，最年长为树干，最年 轻者就是分枝的成员 层次查询可按照任何层次迚行搜索
2
3
根节点的孩子
孩子的孩子，等…
2013年10月25日
Oracle 10g SQL使用
12
层次搜索-LEVEL&LADP
LPAD关键字：
– LPAD 是一个字符函数，表示在左加字符：
COLUMN org_chart FORMAT A12 SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)2,’_’) AS org_chart FROM emp START WITH last_name=’King’ CONNECT BY PRIOR employee_id=manager_id
2013年10月25日
14 rows selected.
Oracle 10g SQL使用
14
修剪枝
使用 WHERE 子句 可消除某个节点，如 WHERE last_name != ’Higgins’
Kochhar
使用 CONNECT BY子句 可消除一个分枝. CONNECT BY PRIOR employee_id = manager_id Kochhar AND last_name != ’Higgins’
DEPTNO EMPNO ENAME ---------- ---------- ---------10 7839 KING 20 7566 JONES 20 7788 SCOTT 20 7876 ADAMS 20 7902 FORD 20 7369 SMITH 30 7698 BLAKE 30 7499 ALLEN 30 7521 WARD 30 7654 MARTIN 30 7844 TURNER 30 7900 JAMES 10 7782 CLARK 10 7934 MILLER