Oracle层次化查询学习总结，oracle层次
层次化查询
文档目录：
1.1 概要 2 1.2 节点术语 2 1.3 使用CONNECT BY 和 START WITH子句3 1.4 层次查询实例 3 1.5 从下向上查询 4 1.6 从层次查询中删除节点和分支5
文档地址：
http://wenku.baidu.com/view/324fb47a700abb68a982fbd1
正文
备注：图片无法显示，请到文库查看
本文地址：http://blog.csdn.net/sunansheng/article/details/46492299
1. 层次化查询
1.1 概要
我们经常可以见到组织为层次的数据，比如一个公司的工作的人员就构成了一个层次，这个层次可以使用树的形式来表示。例如Oracle的表scott.emp，就可以使用如下的树来表示：
下面是查询scott.emp表返回的行：
其中MGR列是一个自引用列，它指回EMPNO列。MGR表示一个员工的管理者或者上级。在ORACLE中可以使用CONNECT BY 和 START WITH子句来查询这种层次结构的数据。
1.2 节点术语
如上图所示，这些元素构成了一棵树。有关节点构成树有一些技术术语，如下：
1) 根节点：根节点是位于树顶端的节点。如上图所示，根节点是“KING”。
2) 父节点：父节点的下面有一个或多个节点。
3) 子节点：子节点上面有一个父节点。
4) 叶节点：叶节点是没有子节点的节点。
1.3 使用CONNECT BY 和 START WITH子句
SELECT语句的CONNECT BY 和 START WITH 子句的语法如下：
|
SELECT [LEVEL],column... FROM TABLE [WHERE where_condition] [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]]
|
其中：
l LEVEL：是一个”伪列”,代表树的第几层。对于根节点来说，LEVEL为1，根节点的子节点层次为2…
l start_condition ：定义了层次化查询的起点。层次化查询必须指定 START WITH 子句。例如，可以指定 start_condition 定义为 EMPNO=7839 (KING)，代表从KING 开始查询。
l prior_condition：定义了父行与子行的关系。当编写层次化查询时必须定义CONNECT BY PRIOR子句。例如，可以将prior_condition定义为ENPNO=MGR，表示父节点的 ENPNO 和子节点的MGR 之间存在关系，也就是说，子节点的 MGR 指向父节点的 ENPNO 。其中跟在PRIOR 关键字后面的代表父节点。
1.4 层次查询实例
1) 查询EMP表的层次关系。查询“JONES “下面的员工
|
SELECT e.empno, e.mgr, e.ename FROM scott.emp e START WITH e.ename = 'JONES' CONNECT BY PRIOR e.empno = e.mgr
查询结果： EMPNO MGR ENAME ----- ----- ---------- 7566 7839 JONES 7788 7566 SCOTT 7876 7788 ADAMS 7902 7566 FORD 7369 7902 SMITH |
预期结果：
2) 使用伪列 LEVEL
|
SELECT LEVEL, e.empno, e.mgr, e.ename FROM scott.emp e START WITH e.ename = 'JONES' CONNECT BY PRIOR e.empno = e.mgr
查询结果： LEVEL EMPNO MGR ENAME ---------- ----- ----- ---------- 1 7566 7839 JONES 2 7788 7566 SCOTT 3 7876 7788 ADAMS 2 7902 7566 FORD 3 7369 7902 SMITH |
1.5 从下向上查询
不一定非要按照从父节点到子节点的顺序从上至下遍历树；也可以从某个子节点开始，从下而上遍历。实现的方法是交换父节点和子节点在CONNECT BY PRIOR子句中的顺序。例如，CONNECT BY PRIOR mgr = empno 可以将子节点的 mgr 连接到父节点的 empno 上。
|
--从叶节点”SMITH”,从下向上查询到根节点： SELECT LEVEL, e.empno, e.mgr, e.ename FROM scott.emp e START WITH e.ename = 'SMITH' CONNECT BY PRIOR e.mgr = e.empno;
查询结果： LEVEL EMPNO MGR ENAME ---------- ----- ----- ---------- 1 7369 7902 SMITH 2 7902 7566 FORD 3 7566 7839 JONES 4 7839 KING |
1.6 从层次查询中删除节点和分支
1. 删除节点
可以用WHERE子句从查询树中除去某个特定的节点，下面这个查询使用WHERE e.ename != 'FORD'子句从结果中除去FORD
|
SELECT LEVEL, e.empno, e.mgr, e.ename FROM scott.emp e WHERE e.ename != 'SCOTT' START WITH e.ename = 'JONES' CONNECT BY PRIOR e.empno = e.mgr;
查询结果： LEVEL EMPNO MGR ENAME ---------- ----- ----- ---------- 1 7566 7839 JONES 3 7876 7788 ADAMS 2 7902 7566 FORD 3 7369 7902 SMITH |
可以看到，尽管 SCOTT 已经从结果中除去了，但是他的下属 ADAMS 仍然在结果中。为了将整个分支都从查询结果中除去，可以再CONNECT BY PRIOR子句中使用AND 子句。例如下面这个例子使用AND e.ename != 'SCOTT' 将SCOTT及其所有下属从结果中除去
2. 删除分支
|
SELECT LEVEL, e.empno, e.mgr, e.ename FROM scott.emp e START WITH e.ename = 'JONES' CONNECT BY PRIOR e.empno = e.mgr AND e.ename != 'SCOTT';
查询结果： LEVEL EMPNO MGR ENAME ---------- ----- ----- ---------- 1 7566 7839 JONES 2 7902 7566 FORD 3 7369 7902 SMITH |