关于oracle树状结构查询

关于oracle 树状结构查询

关于oracle 树状结构查询 .............................................................................................. 1

一、基本概念： . .............................................................................................................. 1

1． 树结构的描述 . ................................................................................................ 1

2． 关于PRIOR . ....................................................................................................... 2

3． 定义查找起始节点 . .......................................................................................... 2

4．运用 LEVEL ......................................................................................................... 2

5．节点和分支的裁剪 . ............................................................................................ 2

6．排序显示 . ............................................................................................................ 3

二、实验与分析 . .............................................................................................................. 3

1．先来看下基本用法： . ........................................................................................ 3

2．我们再来关注一下排序(ORDER BY & ORDER SIBLINGS BY)和伪列(LEVEL)的应

用： . .................................................................................................................................. 4

3．再来看这样一个案例： . .................................................................................... 5

三、一些心得总结 . .......................................................................................................... 7

1．子句的语法书写顺序。 . .................................................................................... 7

2．子句的执行顺序 . ................................................................................................ 8

3．如何理解和记忆“CONNECT BY PRIOR MGR = EMPNO ”的含义呢？ .......... 8

4．前序遍历 . ............................................................................................................ 8

5．排序 . .................................................................................................................... 8

6．伪列LEVEL .......................................................................................................... 8

7．SYS_CONNECT_BY_PATH(COLUMN,CHAR) . ......................................................... 9

Oracle 树查询最重要的就是select...start with... connect by ...prior 语法了。依托于该语法，我们可以将一个表形结构的数据以树的顺序列出来。在下面列述了Oracle 中树型查询的常用查询方式以及经常使用的与树查询相关的Oracle 特性函数等。

一、基本概念：

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子句表明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的联系中。PRIOR 运算符必须放置在连接联系的两列中某一个的前面。对于节点间的父子联系，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， 依此类推。

5．节点和分支的裁剪

在对树结构执行查询时，能够去掉表中的某些行，也能够剪掉树中的一个分支，运用 WHERE 子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。

6．排序显示

象在其它查询中一样，在树结构查询中也能够运用 ORDER BY 子句，改动查询结果的显示顺序，而不必按照遍历树结构的顺序。但需注意与ORDER SIBLINGS BY的区别应用。

二、实验与分析

1．先来看下基本用法：

有一个EMP 表:

EMPNO NAME

10 JACK

11 MARY

12 LUCY

13 DAN

14 TIM

15 DORA

MGR 11 12 15 15 11

这个表形成了一个树结构, MGR表示该用户的领导，

假如我们SQL 这样写:

SELECT * FROM EMP CONNECT BY PRIOR EMPNO = MGR START WITH NAME='LUCY';

这句SQL 意思就是查出LUCY 的所有手下 。

注：SELECT * FROM EMP CONNECT BY MGR =PRIOR EMPNO START WITH NAME='LUCY'; 和上面意思完全一样。

查询结果如下：

EMPNO NAME MGR

12 LUCY 15

11 MARY 12

10 JACK 11

假如SQL 我们这样写:

SELECT * FROM EMP CONNECT BY PRIOR MGR = EMPNO START WITH NAME='LUCY'

这样的话， PRIOR 所在的MGR 就是EMPNO 的子列，即EMPNO 是MGR 的父标识，也就是说我们要完全把关系颠倒过来看，这句SQL 里把MGR 作为每个员工的工号，EMPNO 是每个员工领导的工号，树结构查询总是遍历PRIOR 后的子标识。

所以查询结果是:

EMPNO NAME MGR

12 LUCY 15

15 DORA

(这里MGR 是员工号，EMPNO 是领导工号，所以LUCY 是领导，工号是15，遍历其所有员工，员工的领导工号是15的员工，所以DORA 是其员工)

注意这里很有意思，这种方法遍历出来的结果正好是按照正常理解（EMPNO 是员工号，MGR 是领导工号），对查询用户的所有领导的遍历（DORA 按照表定义的时候是LUCY 的领导）。

PRIOR 如果放在子字段上，则通过START WITH后的字段，遍历所有该字段的下属记录 PRIOR 如果放在父子段上，则通过START WITH后的字段，遍历所有该字段的上属记录 START WITH 是指从哪条记录开始遍历，如果不写则会依次遍历所有记录的下属（或者上属）

如果要限制不想显示一个记录的所有下属（上属），要在CONNECT BY 后限制，如我们要显示LUCY 的所有下属，但不想显示MARY 和其所有下属，则

SELECT * FROM EMP CONNECT BY PRIOR EMPNO = MGR AND NAME'MARY' START WITH NAME='LUCY'

结果：

EMPNO NAME

12 LUCY MGR 15

但如果我们只是不想显示MARY ，但MARY 的下属我们还想显示出来，则在WHERE 后限制

SELECT * FROM EMP WHERE NAME'MARY' CONNECT BY PRIOR EMPNO = MGR START WITH NAME='LUCY'

结果：

EMPNO NAME

12 LUCY

10 JACK

MGR 15 11

2．我们再来关注一下排序(ORDER BY & ORDER SIBLINGS BY) 和伪列(LEVEL)的应用：

有一个DEPT 表：

DEPT_ID PARENT_ID

1

2 1

3 1

4 2

5 2

6 3

7 3

8 3 DEPT_NAME AMOUNT 1 2 1-2 15 1-3 8 1-2-4 10 1-2-5 9 1-3-6 17 1-3-7 5 1-3-8 6

我们来看这样两个需求：

a ．部门1及其所有下级部门，且所有部门按照人数升序排列。

SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

PARENT_ID,DEPT_NAME,AMOUNT

FROM DEPT

START WITH DEPT_ID = 1

CONNECT BY PARENT_ID = PRIOR DEPT_ID

ORDER BY AMOUNT ASC

结果是这样的：

DEPT_ID PARENT_ID DEPT_NAME AMOUNT

1 1 2

7 3 1-3-7 5

8 3 1-3-8 6

3 1 1-3 8

5 2 1-2-5 9

4 2 1-2-4 10

2 1 1-2 15

6 3 1-3-6 17

排序在最后被执行，所以DEPT_ID完全被打乱了，而且层级关系也打乱了。

b ．部门1及其所有下级部门，每个部门的下一级部门之间，按照人数升序排列。(有同一上级的那些部门)

SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

PARENT_ID,DEPT_NAME,AMOUNT

FROM DEPT

START WITH DEPT_ID = 1

CONNECT BY PARENT_ID = PRIOR DEPT_ID

ORDER SIBLINGS BY AMOUNT ASC

结果是这样的：

DEPT_ID PARENT_ID DEPT_NAME AMOUNT

1 1 2

3 1 1-3 8

7 3 1-3-7 5

8 3 1-3-8 6

6 3 1-3-6 17

2 1 1-2 15

5 2 1-2-5 9

4 2 1-2-4 10

同属部门间排序，输出结果可见，部门3、2作为一组进行排序，部门7、8、6为一组，5、4为一组。

3．再来看这样一个案例：

给出A 、B 、C 三个元素，求出这三个元素对应的所有非空子集（含本集），且是顺序无关的。问用SQL 语句该如何实现？

首先，我们可以确认的是，这三个元素所组成的非空子集为：

（A ）（A,B ）（A,B,C ）（A,C ）（B ）（B,C ）（C ）这么七个。

这个问题看起来很简单，实现起来还是要动一番脑筋的。

建表：CREATE TABLE TMP AS (SELECT ROWNUM N FROM DUAL CONNECT BY ROWNUM

SELECT SYS_CONNECT_BY_PATH(N, ',') T, LEVEL N

FROM TMP

CONNECT BY LEVEL

运行结果为：

T N

1 ,1 1

2 ,1,1 2

3 ,1,1,1 3

4 ,1,1,2 3

5 ,1,1,3 3

6 ,1,2 2

7 ,1,2,1 3

8 ,1,2,2 3

9 ,1,2,3 3

10 ,1,3 2

11 ,1,3,1 3

12 ,1,3,2 3

13 ,1,3,3 3

14 ,2 1

15 ,2,1 2

16 ,2,1,1 3

17 ,2,1,2 3

18 ,2,1,3 3

19 ,2,2 2

20 ,2,2,1 3

21 ,2,2,2 3

22 ,2,2,3 3

23 ,2,3 2

24 ,2,3,1 3

25 ,2,3,2 3

26 ,2,3,3 3

27 ,3 1

28 ,3,1 2

29 ,3,1,1 3

30 ,3,1,2 3

31 ,3,1,3 3

32 ,3,2 2

33 ,3,2,1 3

34 ,3,2,2 3

35 ,3,2,3 3

36 ,3,3 2

37 ,3,3,1 3

38 ,3,3,2 3

39 ,3,3,3 3

出来的是不排除重复项的39条记录，如何去除这些多余的元素成了我后面烦恼的事情。

后来在论坛中其他高手的跟帖中找到了一个很完美的解决方案，真是简单而实用：

SELECT SYS_CONNECT_BY_PATH(N, ',') T, LEVEL N

FROM TMP

CONNECT BY N > PRIOR N

运行结果为：

T N

1 ,1 1

2 ,1,2 2

3 ,1,2,3 3

4 ,1,3 2

5 ,2 1

6 ,2,3 2

7 ,3 1

这个方案很好的给出了我们所要的答案。它的关键在于connect n > prior n的使用。上面的语句可以跟前面的connect by level prior n，则集合中所有的元素都会成为集合中任意一个元素的叶子。指定了该遍历方向，则集合中所有大于任意一个元素(A)的元素都会成为该元素(A)对应的叶子。

进一步，如果要求出补集该如何做呢？

如，对于集合1、2、3，有子集1、2，则它的补集即为：3。同样论坛上高手给出了一个很巧妙的方法：

CREATE TABLE A AS

(

SELECT T,

N,

ROW_NUMBER() OVER(PARTITION BY N ORDER BY T) S,

COUNT(1) OVER(PARTITION BY N) + 1 C

FROM (SELECT SYS_CONNECT_BY_PATH(N, ',') T, LEVEL N

FROM (SELECT ROWNUM N FROM DUAL CONNECT BY ROWNUM

CONNECT BY N > PRIOR N)

)

SELECT LTRIM(A.T, ','), LTRIM(B.T, ',')

FROM A, A B

WHERE A.N + B.N = 4 - 1

AND A.S + B.S = B.C

三、一些心得总结

1．子句的语法书写顺序。

SELECT -> FROM -> WHERE -> START WITH -> CONNECT BY -> ORDER BY

WHERE 写在CONNECT BY后面就不行，报错。

2．子句的执行顺序

FROM -> START WITH -> CONNECT BY -> WHERE -> SELECT -> ORDER BY

执行顺序WHERE 在CONNECT BY之后。

可是书写SQL 语句的时候，却只能写前面，注意理解。

3．如何理解和记忆“CONNECT BY PRIOR MGR = EMPNO ”的含义呢？

现在看这个例子似乎很直观，但是今后实际应用时，条件变化后，如何推断查询结果呢？

这里我自己总结一种方法，前提是要理解SQL 语句执行时，是一条一条记录来处理的。

每条满足START WITH语句条件的记录被依次取出，暂且把每次被取出处理的记录，称为当前记录。

“PRIOR MGR”表明从当前记录得到MGR ，

然后" = EMPNO "说明找到表中所有EMPNO 等于当前记录MGR 的记录，也就是找当前记录MGR 所指向的记录。

因为MGR 的取值含义是上级节点，所以说明是向树的根节点方向的搜索。（我的上级是谁？）

反之，如果是“CONNECT BY MGR = PRIOR EMPNO”，“PRIOR ”在EMPNO 一边，就是找所有MGR 等于当前记录EMPNO 的记录，是向树的叶子方向的搜索。（谁的上级是我？）

找到结果记录集以后，从第一条记录开始递归处理，依此类推。

4．前序遍历

由于是递归处理，树的根节点向叶子节点递归查询时，查询节点的顺序是按照树的前序遍历进行的。

5．排序

实验2说明了两种排序的区别。

IN A HIERARCHICAL QUERY, DO NOT SPECIFY EITHER ORDER BY OR GROUP BY, AS THEY WILL DESTROY THE HIERARCHICAL ORDER OF THE CONNECT BY RESULTS. IF YOU WANT TO ORDER ROWS OF SIBLINGS OF THE SAME PARENT, THEN USE THE ORDER SIBLINGS BY CLAUSE. SEE ORDER_BY_CLAUSE.

6．伪列LEVEL

只能随CONNECT BY子句一起使用，是一个整数，代表递归的层次深度。也就是节

点在树中所处深度。

根节点时等于1，根节点的叶子节点的深度等于2，依此类推。

实验2中LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID 正是利用了LEVEL 来为每个层级的字段提供不同的缩进。

7．SYS_CONNECT_BY_PATH(COLUMN,CHAR)

用于返回从根到节点的列值路径。