oracle 树结构查询

ORACLE是一个关系数据库管理系统,它用表的形式组织数据,在某些表中的数据还呈现出树型结构的联系。例如，我们现在讨论雇员信息表EMP，其中含有雇员编号（EMPNO）和经理（MGR）两例，通过这两列反映出来的就是雇员之间领导和被领导的关系。有些雇员领导另一些雇员，有些雇员被领导，还有些雇员领导一些人又被别人领导，他们之间的这种关系就是一种树结构，图1.1表示了EMP表雇员间的这种树结构。
[img]http://dl.iteye.com/upload/attachment/376930/ccd16c06-7d15-3bef-9d11-11fcc6bb438f.png[/img]
在这个树结构中，如果一个节点有直接的下属节点（如图中的JONES 有SCOTT 和FORD），那么称该节点是下属节点的父节点，下属节点为该节点的子节点。通过雇员的EMPNO和MGR可以看出他们之间的父子节点关系，父节点的EMPNO与子节点的MGR相同。在树结构中，有且仅有一个节点无父节点，如图中的KING，该节点被称为根节点。从图上的标记可以看出，只有KING的MGR为空值。除根节点外，任何节点只有一个父节点，有一个，多个或没有子节点。
早扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：
第一步：从根节点开始；
第二步：访问该节点；
第三步：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；
第四步：若该节点为根节点，则访问完毕，否则执行第五步；
第五步：返回到该节点的父节点，并执行第三步骤。
总之：扫描整个树结构的过程也即是中序遍历树的过程。
[b]1． 树结构的描述[/b]
树结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，如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 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。
例1 以树结构方式显示EMP表的数据。

select p.ename, p.empno, p.mgr
  from emp p
connect by prior p.empno = p.mgr
 start with p.ename = 'KING'

[img]http://dl.iteye.com/upload/attachment/376932/eb499903-8b8c-3b7e-8f67-f0b2ce4c8a25.png[/img]
[b]2． 关于PRIOR[/b]
运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。
PRIOR被置于CONNECT BY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。如：
CONNECT BY PRIOR EMPNO=MGR
PIROR运算符被置于CONNECT BY 子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。例如：
CONNECT BY EMPNO=PRIOR MGR
在这种方式中也应指定一个开始的节点。
例2 从SMITH节点开始自底向上查找EMP的树结构。

select p.ename, p.empno, p.mgr
  from emp p
connect by p.empno = prior p.mgr
 start with p.ename = 'SMITH'

[img]http://dl.iteye.com/upload/attachment/376934/292c365b-0463-3685-8440-05400ddcc58c.png[/img]
在这种自底向上的查找过程中，只有树中的一枝被显示，这是因为，在树结构中每一个节点只允许有一个父节点，其查找过程是从开始节点起，找到其父节点，再由其父节点向上，找父节点的父节点。这样一直找到根节点为止，结果就是树中一枝的数据。
备注：例2的另外一种写法

select p.ename, p.empno, p.mgr
  from emp p
connect by prior p.mgr = p.empno
 start with p.ename = 'SMITH'

[b]3． 定义查找起始节点[/b]
在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。
例3 查找JONES直接或间接领导的所有雇员信息。

SELECT EMPNO,ENAME,MGR
  FROM EMP
CONNECT BY PRIOR EMPNO = MGR
 START WITH ENAME = 'JONES'

[img]http://dl.iteye.com/upload/attachment/376948/f4f168a6-f4f5-3e15-aa9c-162b2dc9fb1d.png[/img]
START WITH 不但可以指定一个根节点，还可以指定多个根节点。
例4 查找由FORD和BLAKE 领导的所有雇员的信息。

select p.ename, p.empno, p.mgr
  from emp p
connect by prior p.empno = p.mgr
 start with p.ename in ('FORD', 'BLAKE')

[img]http://dl.iteye.com/upload/attachment/376954/da4f435a-9d22-336f-b5cd-0e9390907bab.png[/img]
在自底向上查询树结构时,也要指定一个开始节点，以此开始向上查找其父节点，直至找到根节点，其结果将是结构树中的一枝数据。
[b]4.使用LEVEL[/b]
在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。图1.2就表示了树结构的层次。


[img]http://dl.iteye.com/upload/attachment/376956/8c80c98e-2bfa-3b13-8b9c-b8c8e2b93853.png[/img]
在查询中，可以使用伪列LEVEL显示每行数据的有关层次。LEVEL将返回树型结构中当前节点的层次，我们可以使用LEVEL来控制对树型结构进行遍历的深度。
例5显示EMP表中的各行数据及层号。

select level, p.ename, p.empno, p.mgr
  from emp p
connect by prior p.empno = p.mgr
 start with p.ename ='KING'

[img]http://dl.iteye.com/upload/attachment/376960/db71a004-82cd-394e-b4a3-f7a8327ef8a4.png[/img]
伪列LEVEL为数值型，可以在SELECT 命令中用语各种计算机。
例6 使用LEVEL改变查询结果的显示形式。

COLUMN EMPLOYEE FORMAT A20;
SELECT EMPNO, RPAD('  ', LEVEL * 3) || ENAME EMPLOYEE, MGR
  FROM EMP p
CONNECT BY PRIOR p.empno = p.mgr
 START WITH ENAME = 'KING';

[img]http://dl.iteye.com/upload/attachment/376962/3ea21673-8ed2-3b3f-807d-68c757cc1c2f.png[/img]
在SELECT使用了函数RPAD，该函数表示以LEVEL*3个空格进行填充，由于不同行处于不同的节点位置，具有不同的LEVEL值，因此填充的空格数将根据各自的层号确定，空格再与雇员名字拼接，结果显示出这种层次关系，也就是说其雇员名字右侧填充的空格数与它的层数有关。
[b]5．节点和分支的裁剪[/b]
在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。

SELECT EMPNO, RPAD('  ', LEVEL * 3) || ENAME EMPLOYEE, MGR
  FROM EMP p
  WHERE ENAME!='SCOTT'
CONNECT BY PRIOR p.empno = p.mgr
 START WITH ENAME = 'KING';

[img]http://dl.iteye.com/upload/attachment/376966/5232876a-7dc5-3e41-9ddb-74a1fc2ba3fa.png[/img]
在这个查询中，仅剪去了树中单个节点SCOTT。若希望剪去树结构中的某个分支，则要用CONNECT BY 子句。CONNECT BY 子句是限定树型结构中的整个分支，既要剪除分支上的单个节点，也要剪除其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。
例8．显示KING领导下的全体雇员信息，除去SCOTT领导的一支。

SELECT EMPNO, RPAD('  ', LEVEL * 3) || ENAME EMPLOYEE, MGR
  FROM EMP p
CONNECT BY PRIOR p.empno = p.mgr
 and ENAME != 'SCOTT'
 START WITH ENAME = 'KING';

[img]http://dl.iteye.com/upload/attachment/376968/d3db18c6-fb8f-31e7-847e-5193871d8f74.png[/img]
这个查询结果就与例7不同，除了剪去单个节点SCOTT外，还将SCOTT的子节点ADAMS剪掉，即把SCOTT这个分支剪掉了。
当然WHERE子句可以和CONNECT BY子句联合使用，这样能够同时剪掉单个节点和树中的某个分支。
例9．显示KING领导全体雇员信息，除去雇员SCOTT，以及BLAKE领导的一支。

SELECT EMPNO, RPAD('  ', LEVEL * 3) || ENAME EMPLOYEE, MGR
  FROM EMP p
 WHERE ENAME != 'SCOTT'
CONNECT BY PRIOR p.empno = p.mgr
       and ENAME != 'BLAKE'
 START WITH ENAME = 'KING';

[img]http://dl.iteye.com/upload/attachment/376970/7ea317ae-9f10-3edb-b91e-4eb34acdcd55.png[/img]
[b]6．排序显示[/b]
象在其它查询中一样，在树结构查询中也可以使用ORDER BY 子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序。

SELECT EMPNO, RPAD('  ', LEVEL * 3) || ENAME EMPLOYEE, MGR
  FROM EMP p
CONNECT BY PRIOR p.empno = p.mgr
 START WITH ENAME = 'KING'
 ORDER BY EMPNO

[img]http://dl.iteye.com/upload/attachment/376974/266e652b-4ead-368e-bbc9-9e7d4cad9927.png[/img]

在使用SELECT 语句来报告树结构报表时应当注意，CONNECT BY子句不能作用于出现在WHERE子句中的表连接。如果需要进行连接，可以先用树结构建立一个视图，再将这个视图与其他表连接，以完成所需要的查询。