Oracle树形查找

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



sql语法

select column,expr….

From table

Where conditions

Start with conditions

Connect by prior conditions



关键字和伪列介绍

Select

部分可以是字段或者表达式,或者伪列，如level，connect_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做节点修剪，只影响scoot，adams不受影响

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 wherepriorempno= 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





伪列Level，prior，connect_by_isleaf，connect_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



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

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





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排号序了