【DB笔试面试478】树形查询（层次查询）可用于哪些场景？

小麦苗DBA宝典

发布于 2019-09-30 16:30:18

1.1K0

发布于 2019-09-30 16:30:18

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

题目部分

树形查询（层次查询）可用于哪些场景？

答案部分

在实际开发中，如果表中数据具有逻辑上的层次结构，那么可以使用层次查询以更直观地显示查询结果（包括数据本身以及数据之间的层次关系）。树形结构的关系可以控制遍历树的方向，是自上而下，还是自下而上，还可以确定层次的开始点（ROOT）的位置。层次查询语句正是从这两个方面来确定的，START WITH确定开始点，CONNECT BY确定遍历的方向。

树形结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，例如EMP表中的EMPNO和MGR列。EMPNO表示该雇员的编号，MGR表示该雇员领导的编号，即子结点的MGR值等于父结点的EMPNO值。在表的每一行中都有一个表示父结点的MGR（除根结点外），通过每个结点的父结点，就可以确定整个树结构。

层次查询的基本语法格式如下所示：

SELECT <LEVEL|字段| 表达式>

FROM < 目标表>

[WHERE <查询限定条件>]

[START WITH < 指定查询层次根结点应满足的条件>]

CONNECT BY PRIOR 当前表字段=级联表字段 <指定父结点和子结点（父行和子行）间的关联关系>

在使用层次查询的过程中，需要注意以下几点内容：

1、层次查询是通过START WITH和CONNECT BY子句标识的。

2、其中，LEVEL关键字是可选的，表示等级，代表树的第几层。对根结点来说，LEVEL返回1，根结点的子结点返回2，以此类推。LEVEL是层次查询的一个伪列，如果有LEVEL，那么必须有CONNECT BY，而START WITH可以没有。利用CONNECT BY可以快速构造数据，例如SQL语句“SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=1000;”返回1至1000共1000行整数。

3、FROM之后可以是TABLE或VIEW。对于TABLE来说，只能是一个TABLE；而对于VIEW来说，这个VIEW不能包含JOIN。

4、WHERE条件限制了查询返回的行，但是不影响层次关系，属于结点截断，但是这个被截断的结点的下层CHILD不受影响。[WHERE <查询限定条件>]是根据CONNECT BY和START WITH选择出来的记录进行过滤的，是针对单条记录的过滤，不会考虑树的结构。

5、START WITH表示开始结点，限定作为搜索起始点的条件，如果是自上而下的搜索，那么是限定作为根结点的条件，如果是自下而上的搜索，那么是限定作为叶子结点的条件。START WITH子句为可选项，用来标识哪个结点作为查找树型结构的根结点。若该子句被省略，则表示所有满足查询条件的行作为根结点，这里可以用一个子查询指定多个根结点。

6、CONNECT BY PRIOR是指定父子关系，其中，PRIOR的位置不一定要在CONNECT BY之后，对于一个真实的层次关系，这也是必须的。CONNECT BY指定构造树的条件，以及对树分支的过滤条件，在这里执行的过滤会把符合条件的记录及其下的所有子结点都过滤掉；CONNECT BY也可以带多个条件，例如CONNECT BY PRIOR ID=MANAGER_ID AND ID>10，但是CONNECT BY中不能有子查询。如果省略CONNECT BY后面的PRIOR关键词，那么只能查询到符合条件的起始行，并不进行递归查询。

7、CONNECT BY与START WITH语句摆放的先后顺序不影响查询的结果。

8、START WITH与CONNECT BY PRIOR语句完成递归记录，形成一棵树形结构，通常可以在具有层次结构的表中使用。

9、PRIOR和START WITH关键字是可选项。

10、PRIOR运算符必须放置在连接关系的两列中某一个的前面。对于结点间的父子关系，PRIOR运算符所在的一侧表示父结点，等号的另一侧表示子结点，从而确定查找树结构的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。

树形查询示例：

SYS@lhrdb> SELECT * FROM SCOTT.EMP;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
14 rows selected.
SELECT LEVEL P_LEVEL,
       T.EMPNO,
       T.ENAME,
       T.MGR,
       (LPAD(' ', 6 * (LEVEL - 1)) || LEVEL || ':' || T.ENAME || '(' ||
       T.EMPNO || ')') "name(id)",
       SUBSTR(SYS_CONNECT_BY_PATH(T.ENAME, '=>'), 3) ALL_NAME_LEVEL,
       CONNECT_BY_ROOT(T.ENAME) ROOT,
       DECODE(CONNECT_BY_ISLEAF, 1, 'Y', 0, 'N') IS_LEAF
  FROM SCOTT.EMP T
 START WITH MGR IS NULL
CONNECT BY NOCYCLE MGR = PRIOR EMPNO;