Oracle树形结构的sql语句-白红宇

Oracle树形结构的sql语句

阅读量：5732 次

发布时间：2019-06-18

本文共 6412 字，大约阅读时间需要 21 分钟。

一、基本概念：

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 MGR

10 JACK 11

11 MARY 12

12 LUCY 15

13 DAN 15

14 TIM 11

15 DORA

这个表形成了一个树结构, 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 MGR

12 LUCY 15

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

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

结果：

EMPNO NAME MGR

12 LUCY 15

10 JACK 11

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

有一个DEPT表：

DEPT_ID PARENT_ID DEPT_NAME AMOUNT

1 1 2

2 1 1-2 15

3 1 1-3 8

4 2 1-2-4 10

5 2 1-2-5 9

6 3 1-3-6 17

7 3 1-3-7 5

8 3 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 < 4)

我的第一次尝试是这样的：

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

FROM TMP

CONNECT BY LEVEL < 4

运行结果为：

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 <= N结合起来理解。如果没有指定n > prior n，则集合中所有的元素都会成为集合中任意一个元素的叶子。指定了该遍历方向，则集合中所有大于任意一个元素(A)的元素都会成为该元素(A)对应的叶子。

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

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

CREATE TABLE A AS

(

SELECT T,

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 < 4)

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.