Oracle数据库中常要处理一些父子关系的记录，在OLTP中要用得多一些，oracle提供了递归查询可以很容易的满足这个需求，oracle的递归查询通过start with [condition] connect by prior [condition]实现。我们先来看看emp表的例子：
PgSQL
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL>select*fromemp;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369SMITHCLERK79021980/12/17800.0020
7499ALLENSALESMAN76981981/2/201600.00300.0030
7521WARDSALESMAN76981981/2/221250.00500.0030
7566JONESMANAGER78391981/4/22975.0020
7654MARTINSALESMAN76981981/9/281250.001400.0030
7698BLAKEMANAGER78391981/5/12850.0030
7782CLARKMANAGER78391981/6/92450.0010
7788SCOTTANALYST75661987/4/193000.0020
7839KINGPRESIDENT1981/11/175000.0010
7844TURNERSALESMAN76981981/9/81500.000.0030
7876ADAMSCLERK77881987/5/231100.0020
7900JAMESCLERK76981981/12/3950.0030
7902FORDANALYST75661981/12/33000.0020
7934MILLERCLERK77821982/1/231300.0010
14rowsselected
emp表中MGR列表示了当前员工的上级编号，现在用简单的树形结果展示每个员工的关系
PgSQL
SQL> SELECT lpad('-',LEVEL*2)||empno||':'||ename as Tree,level FROM emp
2 START WITH mgr IS NULL CONNECT BY PRIOR empno=mgr;
TREE LEVEL
-------------------------------------------------------------------------------- ----------
-7839:KING 1
-7566:JONES 2
-7788:SCOTT 3
-7876:ADAMS 4
-7902:FORD 3
-7369:SMITH 4
-7698:BLAKE 2
-7499:ALLEN 3
-7521:WARD 3
-7654:MARTIN 3
-7844:TURNER 3
-7900:JAMES 3
-7782:CLARK 2
-7934:MILLER 3
14 rows selected
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL>SELECTlpad('-',LEVEL*2)||empno||':'||enameasTree,levelFROMemp
2STARTWITHmgrISNULLCONNECTBYPRIORempno=mgr;
TREELEVEL
-------------------------------------------------------------------------------- ----------
-7839:KING1
-7566:JONES2
-7788:SCOTT3
-7876:ADAMS4
-7902:FORD3
-7369:SMITH4
-7698:BLAKE2
-7499:ALLEN3
-7521:WARD3
-7654:MARTIN3
-7844:TURNER3
-7900:JAMES3
-7782:CLARK2
-7934:MILLER3
14rowsselected
通过树形展示，就可以清晰的看出来每个员工的从属关系，员工7876属于7788管理，7788属于7566管理，7839就是所有员工的老大了。我们来分析下这个递归sql，这里使用了lpad函数向左填充，level字段显示当前的层次级别，start with指定了层次开始的条件(从哪里开始遍历树)，即mgr is null满足这个条件的就是层次树的最顶层，connect by prior指定层之间的关联条件，即哪一行是上层行的子行，这里的关联条件是empno=mgr，这样就可以指定员工和上层管理的层次关系。
上面一种sql是层次向下查询，如果想查询7876的上级关系，可以稍微改一下层次开始条件和层之间的关联条件
PgSQL
SQL> SELECT empno||':'||ename as Tree,level FROM emp
2 START WITH empno=7876 CONNECT BY PRIOR mgr=empno;
TREE LEVEL
--------------------------------------------------- ----------
7876:ADAMS 1
7788:SCOTT 2
7566:JONES 3
7839:KING 4
1
2
3
4
5
6
7
8
SQL>SELECTempno||':'||enameasTree,levelFROMemp
2STARTWITHempno=7876CONNECTBYPRIORmgr=empno;
TREELEVEL
--------------------------------------------------- ----------
7876:ADAMS1
7788:SCOTT2
7566:JONES3
7839:KING4
这里就直接简单明了的能看到7876的上级层次关系。这里需要注意connect by prior后面的关联条件，一定要注意那个条件在左，哪个在右。而且也要注意prior的位置，例如sql也可以写成下面这样：
PgSQL
SQL> SELECT lpad('-',LEVEL*2)||empno||':'||ename as Tree,level FROM emp
2 START WITH mgr IS NULL CONNECT BY mgr=PRIOR empno;
TREE LEVEL
-------------------------------------------------------------------------------- ----------
-7839:KING 1
-7566:JONES 2
-7788:SCOTT 3
-7876:ADAMS 4
-7902:FORD 3
-7369:SMITH 4
-7698:BLAKE 2
-7499:ALLEN 3
-7521:WARD 3
-7654:MARTIN 3
-7844:TURNER 3
-7900:JAMES 3
-7782:CLARK 2
-7934:MILLER 3
14 rows selected
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL>SELECTlpad('-',LEVEL*2)||empno||':'||enameasTree,levelFROMemp
2STARTWITHmgrISNULLCONNECTBYmgr=PRIORempno;
TREELEVEL
-------------------------------------------------------------------------------- ----------
-7839:KING1
-7566:JONES2
-7788:SCOTT3
-7876:ADAMS4
-7902:FORD3
-7369:SMITH4
-7698:BLAKE2
-7499:ALLEN3
-7521:WARD3
-7654:MARTIN3
-7844:TURNER3
-7900:JAMES3
-7782:CLARK2
-7934:MILLER3
14rowsselected
总结：
1． 树结构的描述
树结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，如EMP表中的EMPNO和MGR。EMPNO表示该雇员的编号，MGR表示领导该雇员的人的编号，即子节点的MGR值等于父节点的EMPNO值。在表的每一行中都有一个表示父节点的MGR(除根节点外)，通过每个节点的父节点，就可以确定整个树结构。
在SELECT命令中使用CONNECT BY 和START WITH 子句可以查询表中的树型结构关系。其命令格式如下：
SELECT 。。。
CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 裂名2}
[START WITH]；
其中：CONNECT BY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。
START WITH： 不但可以指定一个根节点，还可以指定多个根节点。
2． 关于PRIOR
运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。
PRIOR被置于CONNECT BY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。如：
CONNECT BY PRIOR EMPNO=MGR
PIROR运算符被置于CONNECT BY 子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。例如：
CONNECT BY EMPNO=PRIOR MGR
在这种方式中也应指定一个开始的节点。
3． 定义查找起始节点
在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。
4．使用LEVEL
在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。图1.2就表示了树结构的层次。
5．节点和分支的裁剪
在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点(自顶向下检索时)或前辈节点(自底向顶检索时)。
6．排序显示
象在其它查询中一样，在树结构查询中也可以使用ORDER BY 子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序。
转载请注明: 版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
最后编辑：2015-07-05作者：Jerry
一个积极向上的小青年，热衷于分享--Focus on DB,BI,ETL
2616
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
