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