已归录
树结构查询
ORACLE 是一个关系数据库管理系统，它用表的形式组织数据，在某些表中的数据还呈现出树型结构的联系。例如，EMP 中含有雇员编号（EMPNO）和经理（MGR）两列，通过这两列反映出来的就是雇员之间领导和被领导的关系。他们之间的这种关系就是一种树结构。
下表是 EMP 表树结构图：
| 7839 | |||||||
| 7566 | 7698 | 7782 | |||||
| 7788 | 7902 | 7499 | 7521 | 7654 | 7844 | 7900 | 7934 |
| 7876 | 7369 | ||||||
遍历有两个方向：
- top--down 自上而下，说白了父亲找儿子，一个父亲可能有几个儿子，一个儿子可能有几个孙子，自上而下遍历是一个不能丢儿子的过程，顺序以左为先。
- down--top 自底向上，简单说就是儿子找父亲，一个儿子只能有一个父亲，所以自底向上的顺序应该是：孙子->儿子-->父亲-->爷爷。
树结构的描述
树结构的数据存放在表中，数据之间的层次关系即父子关系，在表的每一行中都有一个表示父节点的 MGR（除根节点外），通过每个节点的父节点，就可以确定整个树结构。
在 SELECT 命令中使用 CONNECT BY 和 START WITH 子句可以查询表中的树型结构关系。其命令格式如下：
SELECT ...
CONNECT BY {PRIOR 列名 1=列名 2|列名 1=PRIOR 列名 2}
[START WITH]；CONNECT BY 子句非常关键，它包含两个概念：
CONNECT BY PRIOR 列名 1 = 列名 2：
1）top--down
2）列名 1 是父， 列名 2 是子
CONNECT BY 列名 1 = PRIOR 列名 2：
1）down--up
2）列名 1 是子， 列名 2 是父
也就是说 PRIOR 运算符在的一侧表示父节点，另一侧表示子节点，而 PRIOR 在 = 左边还是右边确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。
START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。
树形查找示例
例 1：以树结构方式显示 EMP 表的数据。
SQL> select empno,ename,mgr from emp
connect by prior empno=mgr
start with empno=7839;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7698 BLAKE 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
7782 CLARK 7839
7934 MILLER 7782
14 rows selected.仔细看 empno 这一列输出的顺序，就是前面树状结构每一条分支（从根节点开始）的结构：
| 7839 | |||||||
| 7566 | 7698 | 7782 | |||||
| 7788 | 7902 | 7499 | 7521 | 7654 | 7844 | 7900 | 7934 |
| 7876 | 7369 | ||||||
层次查询执行逻辑如下：
- 1).确定上一行（相对于步骤2 中的当前行），若 start with 子句存在，则以该语句确定的行为上一行，若不存在则将所有的数据行视为上一行。
- 2).从上一行出发，扫描除该行之外所有数据行。 [该步骤中扫描得到的所有行中的某一行我们定义为当前行]
- 3).匹配条件 (prior empno) = mgr [prior 意思是之前的，指上一行]
匹配条件含义：当前行字段 mgr 的值等于上一行字段 empno 中的值，若满足则取出该行，并将 level + 1。
匹配完所有行记录后，将满足条件的行作为上一行，执行步骤 b，c。直到所有行匹配结束。
例 2：从 SMITH 节点开始自底向上查找 EMP 的树结构。
SQL> select empno,ename,mgr
from emp
connect by empno=prior mgr
start with empno=7369;
EMPNO ENAME MGR
---------- ---------- ----------
7369 SMITH 7902
7902 FORD 7566
7566 JONES 7839
7839 KING在这种自底向上的查找过程中，只有树中的一枝被显示。
定义查找起始节点
在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。
例 3：查找 7566(JONES) 直接或间接领导的所有雇员信息。
SQL> select empno, ename, mgr
from emp
connect by prior empno = mgr
start with empno=7566;
EMPNO ENAME MGR
---------- ---------- ----------
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902START WITH 不但可以指定一个根节点，还可以指定多个根节点。例如：
例 4：查找由 FORD 和 BLAKE 领导的所有雇员的信息。
SQL>SELECT EMPNO,ENAME,MGR
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
START WITH ENAME IN ('FORD','BLAKE');
EMPNO ENAME MGR
---------- ---------- ----------
7902 FORD 7566
7369 SMITH 7902
7698 BLAKE 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
8 rows selected.使用 LEVEL 显示层级
在查询中，可以使用伪列 LEVEL 显示每行数据的有关层次。LEVEL 将返回树型结构中当前节点的层次。伪列 LEVEL 为数值型，可以在 SELECT 命令中用于各种计算。
例 5：使用 LEVEL 改变查询结果的显示形式。
SQL> SET PAGES 100;
SQL> COLUMN LEVEL FORMAT A20；
SQL> SELECT LPAD(LEVEL,LEVEL*3,'*') as "LEVEL",EMPNO,ENAME,MGR
FROM EMP
CONNECT BY PRIOR EMPNO=MGR
START WITH ENAME='KING';
LEVEL EMPNO ENAME MGR
-------------------- ---------- ---------- ----------
**1 7839 KING
*****2 7566 JONES 7839
********3 7788 SCOTT 7566
***********4 7876 ADAMS 7788
********3 7902 FORD 7566
***********4 7369 SMITH 7902
*****2 7698 BLAKE 7839
********3 7499 ALLEN 7698
********3 7521 WARD 7698
********3 7654 MARTIN 7698
********3 7844 TURNER 7698
********3 7900 JAMES 7698
*****2 7782 CLARK 7839
********3 7934 MILLER 7782
14 rows selected.这儿使用了 * 号填充是为了方便查看，将 * 号换成空格将显示得更加一目了然，如果不使用 LPAD 处理 LEVEL，将整齐地显示一列级别编号，就不能真观地看到层次关系。
例 6：只查看 2 级的员工信息：
SQL> select t.* from (
select level LNUM, empno, ename, mgr
from emp
connect by prior empno = mgr
start with ename='KING') t
where LNUM=2;
LNUM EMPNO ENAME MGR
---------- ---------- ---------- ----------
2 7566 JONES 7839
2 7698 BLAKE 7839
2 7782 CLARK 7839
节点和分支的裁剪
在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用 WHERE 子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。
例 7：以树结构方式显示 EMP 表的数据，但不展示 SCOTT 员工：
SQL> SET PAGES 100;
SQL> COLUMN LEVEL FORMAT A20;
SQL> SELECT LPAD(LEVEL,LEVEL*3,' ') as "LEVEL",EMPNO,ENAME,MGR
FROM EMP
WHERE ENAME<>'SCOTT'
CONNECT BY PRIOR EMPNO=MGR
START WITH ENAME='KING';
LEVEL EMPNO ENAME MGR
-------------------- ---------- ---------- ----------
1 7839 KING
2 7566 JONES 7839
4 7876 ADAMS 7788
3 7902 FORD 7566
4 7369 SMITH 7902
2 7698 BLAKE 7839
3 7499 ALLEN 7698
3 7521 WARD 7698
3 7654 MARTIN 7698
3 7844 TURNER 7698
3 7900 JAMES 7698
2 7782 CLARK 7839
3 7934 MILLER 7782
13 rows selected.这个查询结果与上面的全树结果进行比较，会发现少了一条记录。在这个查询中，仅剪去了树中单个节点 SCOTT。
若希望剪去树结构中的某个分支，则要用 CONNECT BY 子句。CONNECT BY 子句是限定树型结构中的整个分支，既要剪除分支上的单个节点，也要剪除其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。
例 8：显示 KING 领导下的全体雇员信息，除去 SCOTT 领导的一支。
SQL> SET PAGES 100;
SQL> COLUMN LEVEL FORMAT A20;
SQL> SELECT LPAD(LEVEL,LEVEL*3,' ') as "LEVEL",EMPNO,ENAME,MGR
FROM EMP
CONNECT BY PRIOR EMPNO = MGR AND ENAME!='SCOTT'
START WITH ENAME = 'KING';
LEVEL EMPNO ENAME MGR
-------------------- ---------- ---------- ----------
1 7839 KING
2 7566 JONES 7839
3 7902 FORD 7566
4 7369 SMITH 7902
2 7698 BLAKE 7839
3 7499 ALLEN 7698
3 7521 WARD 7698
3 7654 MARTIN 7698
3 7844 TURNER 7698
3 7900 JAMES 7698
2 7782 CLARK 7839
3 7934 MILLER 7782
12 rows selected.这个查询结果就除了剪去单个节点 SCOTT 外，还将 SCOTT 的子节点 ADAMS 剪掉，即把 SCOTT 这个分支剪掉了。
当然 WHERE 子句可以和 CONNECT BY 子句联合使用，这样能够同时剪掉单个节点和树中的某个分支。
例 9：显示 KING 领导全体雇员信息，除去雇员SCOTT，以及 BLAKE 领导的一支。
SQL> SET PAGES 100;
SQL> COLUMN LEVEL FORMAT A20;
SQL> SELECT LPAD(LEVEL,LEVEL*3,' ') as "LEVEL",EMPNO,ENAME,MGR
FROM EMP
WHERE ENAME<>'SCOTT'
CONNECT BY PRIOR EMPNO=MGR AND ENAME!='BLAKE'
START WITH ENAME='KING';
LEVEL EMPNO ENAME MGR
-------------------- ---------- ---------- ----------
1 7839 KING
2 7566 JONES 7839
4 7876 ADAMS 7788
3 7902 FORD 7566
4 7369 SMITH 7902
2 7782 CLARK 7839
3 7934 MILLER 7782
7 rows selected.最后注意一下，在使用 SELECT 语句来报告树结构报表时，CONNECT BY 子句不能作用于出现在 WHERE 子句中的表连接。如果需要进行连接，可以先用树结构建立一个视图，再将这个视图与其他表连接，以完成所需要的查询
