ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle树形查找

Oracle树形查找

原创 Linux操作系统 作者:oracle_kai 时间:2009-04-03 19:03:54 0 删除 编辑

Oracle树形查找

Oracle是一种关系型数据库,在表中不可能以层次的关系存放数据,但是oracle提供了层次(树形)查询语句,使用树的遍历来获得层次关系的数据。

 

sql语法

select column,expr….

From table

Where conditions

Start with conditions

Connect by prior conditions

 

关键字和伪列介绍

Select

部分可以是字段或者表达式, 或者伪列,如levelconnect_by_isleaf .

From

From后面可以是table,view但是只能是一个table,view中不能有多个表连接.

Where

条件限制了查询返回的行,但是其只影响节点自身,该节点的下层child不受影响

,属于节点的截断.

start with

确定遍历查询的开始点,可以是子查询,也可以不指定,不指定表示每个节点都作为起始节点来遍历一遍

connect by prior

确定遍历的方向,即是找子孙节点还是找祖先节点,遍历方向分为:自上向下,自下向上.

如果prior 字段放在父字段前面,则表示要做自下向上的遍历;

如果prior 字段放在子字段段前面,则表示要做自上向下的遍历;

和关键字 prior 放在=号左边右边没关系。

 

父字段:指明记录上一节点的字段 emp表中的mgr字段

子字段:用来和父字段连接的字段,如emp标中的empno字段

level  

查询的起始节点level 1,子孙依次增加,代表每个节点在家族树中的层次关系

connect_by_isleaf  

是否叶子节点,如果查询时自顶向下,则叶子节点为1,如果自下向上,则根节点为1

connect _by_root  column  

查找子节点,叶子节点对应的根节点,10g新增的,这个很有用

prior 

表示父节点,可以用在selelct部分,也可以用在connect by部分

nocycle                  

如果出现循环,在connect by中制定nocycle 选项,查询将跳过循环部分的节点,避免10g之前的抱错。

connect_iscycle  

如果出现循环,则为1,可以找出哪一条记录出现了循环。需要在connect by中加上nocycle 选项

sys_connect_by_path

path的顺序把字段连起来,做行列转换的时候需要。

 

Siblings

用于对树形遍历结果的排序,普通的排序会打乱树形遍历结果的层次关系,加上此关键字,可以不改变树形遍历结果的层次关系,只是在每一层内部按关键字排序,相当于做组内排序了,用法为order siblings by col

 

节点修剪和分支修剪

where子句的限制将会做节点的修剪,但是其后代不会受到影响,相对应,connect by 中加上条件,将会把满足条件的节点以及后代修剪,属于分支修剪。

     

格式化输出

利用lpad 函数和level,可以格式化输出记录间父子层次关系,方便阅读。

 

应用例子

 

利用lpad 函数和level伪列,格式化输出

SQL>  select empno,ename,lpad(empno,length(empno)+(level-1)*4,'-') from emp  start with mgr is null connect by prior empno=mgr;

 

EMPNO ENAME      LPAD(EMPNO,LENGTH(EMPNO)+(LEVE

----- ---------- --------------------------------------------------------------------------------

 7839 KING       7839

 7566 JONES      ----7566

 7788 SCOTT      --------7788

 7876 ADAMS      ------------7876

 7902 FORD       --------7902

 7369 SMITH      ------------7369

 7698 BLAKE      ----7698

 7499 ALLEN      --------7499

 7521 WARD       --------7521

 7654 MARTIN     --------7654

 7844 TURNER     --------7844

 7900 JAMES      --------7900

 7782 CLARK      ----7782

 7934 MILLER     --------7934

 

自上向下,自下向上遍历查询

自下向上遍历

SQL> select empno,ename,level from emp start with empno=7788 connect by prior mgr=empno;

 

EMPNO ENAME           LEVEL

----- ---------- ----------

 7788 SCOTT               1

 7566 JONES               2

 7839 KING                3

 

自上向下遍历

SQL> select empno,ename,level from emp start with empno=7788 connect by prior empno=mgr;

 

EMPNO ENAME           LEVEL

----- ---------- ----------

 7788 SCOTT               1

 7876 ADAMS               2

 

上面的sql语句等同于下面的sql

SQL> select empno,ename,level from emp start with empno=7788 connect by mgr=prior empno;

 

EMPNO ENAME           LEVEL

----- ---------- ----------

 7788 SCOTT               1

 7876 ADAMS               2

 

 

节点修剪和分支修剪

 

比如对scott 7788做节点修剪,只影响scootadams不受影响

SQL> select empno,ename,lpad(empno,length(empno)+(level-1)*4,'-') from emp where empno<>7788 start with mgr is null connect by prior empno=mgr;

 

EMPNO ENAME      LPAD(EMPNO,LENGTH(EMPNO)+(LEVE

----- ---------- --------------------------------------------------------------------------------

 7839 KING       7839

 7566 JONES      ----7566

 7876 ADAMS      ------------7876

 7902 FORD       --------7902

 7369 SMITH      ------------7369

 7698 BLAKE      ----7698

 7499 ALLEN      --------7499

 7521 WARD       --------7521

 7654 MARTIN     --------7654

 7844 TURNER     --------7844

 7900 JAMES      --------7900

 7782 CLARK      ----7782

 7934 MILLER     --------7934

 

对比下面的分支修剪,可以看到2者的区别,下面的语句不但scott修剪掉了,而且其子孙节点adams也被修剪掉了

 

SQL> select empno,ename,lpad(empno,length(empno)+(level-1)*4,'-') from emp  start with mgr is null connect by prior empno=mgr and empno<>7788

EMPNO ENAME      LPAD(EMPNO,LENGTH(EMPNO)+(LEVE

----- ---------- --------------------------------------------------------------------------------

 7839 KING       7839

 7566 JONES      ----7566

 7902 FORD       --------7902

 7369 SMITH      ------------7369

 7698 BLAKE      ----7698

 7499 ALLEN      --------7499

 7521 WARD       --------7521

 7654 MARTIN     --------7654

 7844 TURNER     --------7844

 7900 JAMES      --------7900

 7782 CLARK      ----7782

 7934 MILLER     --------7934

 

Where条件和connect by中的条件也可以混合使用

 

查找父结点是7689且节点empno不是7521的所有纪录

SQL> select empno,ename,lpad(empno,length(empno)+(level-1)*4,'-') from emp  where prior empno= 7698 start with mgr is null connect by prior empno=mgr and empno<>7521;

 

EMPNO ENAME      LPAD(EMPNO,LENGTH(EMPNO)+(LEVE

----- ---------- --------------------------------------------------------------------------------

 7499 ALLEN      --------7499

 7654 MARTIN     --------7654

 7844 TURNER     --------7844

 7900 JAMES      --------7900

 

 

伪列Levelpriorconnect_by_isleafconnect_by_root的使用

SQL> select empno,ename,level,prior empno p_empno,connect_by_isleaf isleaf,connect_by_root empno r_empno from emp  start with mgr is null connect by prior empno=mgr ;

 

EMPNO ENAME           LEVEL    P_EMPNO     ISLEAF    R_EMPNO

----- ---------- ---------- ---------- ---------- ----------

 7839 KING                1                     0       7839

 7566 JONES               2       7839          0       7839

 7788 SCOTT               3       7566          0       7839

 7876 ADAMS               4       7788          1       7839

 7902 FORD                3       7566          0       7839

 7369 SMITH               4       7902          1       7839

 7698 BLAKE               2       7839          0       7839

 7499 ALLEN               3       7698          1       7839

 7521 WARD                3       7698          1       7839

 7654 MARTIN              3       7698          1       7839

 7844 TURNER              3       7698          1       7839

 7900 JAMES               3       7698          1       7839

 7782 CLARK               2       7839          0       7839

 7934 MILLER              3       7782          1       7839

 

伪列connect_by_iscycle, sys_connect_by_path的使用

 

SQL> select empno,ename,connect_by_iscycle iscycle,sys_connect_by_path(empno,'/') path_empno  from emp  start with mgr is null connect by nocycle prior empno=mgr ;

 

EMPNO ENAME         ISCYCLE PATH_EMPNO

----- ---------- ---------- --------------------------------------------------------------------------------

 7839 KING                0 /7839

 7566 JONES               0 /7839/7566

 7788 SCOTT               0 /7839/7566/7788

 7876 ADAMS               0 /7839/7566/7788/7876

 7902 FORD                0 /7839/7566/7902

 7369 SMITH               0 /7839/7566/7902/7369

 7698 BLAKE               0 /7839/7698

 7499 ALLEN               0 /7839/7698/7499

 7521 WARD                0 /7839/7698/7521

 7654 MARTIN              0 /7839/7698/7654

 7844 TURNER              0 /7839/7698/7844

 7900 JAMES               0 /7839/7698/7900

 7782 CLARK               0 /7839/7782

 7934 MILLER              0 /7839/7782/7934

 

 

查看king 下每一代的个数及平均sal

 

SQL> select level,count(*),trunc(avg(sal)) from emp  start with mgr is null connect by prior empno=mgr group by level;

 

     LEVEL   COUNT(*) TRUNC(AVG(SAL))

---------- ---------- ---------------

         1          1            5000

         2          3            2758

         4          2             950

         3          8            1731

 

确认节点之间是否存在层级关系,如存在,列出层级关系

这可应用在产品的组装关系,员工的上下级领导关系的判断,如下面语句判断78397788是否有层级关系,如果返回结果,则表示存在层级关系

 

 

SQL> select empno,ename,level,sys_connect_by_path(empno,'/') from emp where empno=7788 start with empno=7839 connect by mgr=prior empno;

 

EMPNO ENAME           LEVEL SYS_CONNECT_BY_PATH(EMPNO,'/')

----- ---------- ---------- --------------------------------------------------------------------------------

 7788 SCOTT               3 /7839/7566/7788

 

查找每个部分的老大,增加了限制条件prior deptno<> deptno

 

SQL> select deptno,empno,ename,level from emp start with empno=7839 connect by mgr=prior empno and prior deptno<> deptno;

 

DEPTNO EMPNO ENAME           LEVEL

------ ----- ---------- ----------

    10  7839 KING                1

    20  7566 JONES               2

    30  7698 BLAKE               2

 

 

树形遍历排序 order siblings by

 

先看一下用普通order取排序的情况

SQL> select deptno,empno,ename,lpad(empno,length(empno)+(level-1)*4,'-') from emp  start with mgr is null connect by prior empno=mgr order by ename;

 

DEPTNO EMPNO ENAME      LPAD(EMPNO,LENGTH(EMPNO)+(LEVE

------ ----- ---------- --------------------------------------------------------------------------------

    20  7876 ADAMS      ------------7876

    30  7499 ALLEN      --------7499

    30  7698 BLAKE      ----7698

    10  7782 CLARK      ----7782

    20  7902 FORD       --------7902

    30  7900 JAMES      --------7900

    20  7566 JONES      ----7566

    10  7839 KING       7839

    30  7654 MARTIN     --------7654

    10  7934 MILLER     --------7934

    20  7788 SCOTT      --------7788

    20  7369 SMITH      ------------7369

    30  7844 TURNER     --------7844

    30  7521 WARD       --------7521

 

原先层次关系已经被打乱了,order 关键词后面加上silbings

 

SQL> select deptno,empno,ename,lpad(empno,length(empno)+(level-1)*4,'-') from emp  start with mgr is null connect by prior empno=mgr order siblings by ename;

 

DEPTNO EMPNO ENAME      LPAD(EMPNO,LENGTH(EMPNO)+(LEVE

------ ----- ---------- --------------------------------------------------------------------------------

    10  7839 KING       7839

    30  7698 BLAKE      ----7698

    30  7499 ALLEN      --------7499

    30  7900 JAMES      --------7900

    30  7654 MARTIN     --------7654

    30  7844 TURNER     --------7844

    30  7521 WARD       --------7521

    10  7782 CLARK      ----7782

    10  7934 MILLER     --------7934

    20  7566 JONES      ----7566

    20  7902 FORD       --------7902

    20  7369 SMITH      ------------7369

    20  7788 SCOTT      --------7788

    20  7876 ADAMS      ------------7876

 

层次关系没有变,每一层(level)内按ename排号序了

 

 

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10159839/viewspace-584603/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-20

  • 博文量
    48
  • 访问量
    174191