Oracle PL/SQL 基础教程（8）复杂查询（五）

SQL干货分享 2021-08-13

640

（CSDN博主：写代码也要符合基本法）

各位看官老爷大家早上好，又到了每周三基础级教程的分享时间

在前面的几节课中，我们已经将基本的 SQL 查询语句较为深入的探讨了一番，那么在开始新的内容之前，我想先补充一个知识点：SELECT 语句各部分的执行顺序

SELECT 语句各部分执行顺序

FROM：组装来自不同数据源的数据（如实现表联结）；
WHERE：执行指定的筛选谓词实现上述数据的筛选；
GROUP BY：数据分组；
HAVING：根据指定的聚合结果筛选分组；
SELECT：从结果集中选择字段（注：a.表达式（如聚合运算等）先行计算；b.分组查询在分组前有预选字段的过程，这里SELECT从已形成的结果集中复选字段（或表达式））；
DISTINCT：去重；
ORDER BY：结果集排序装饰

总结完上面的内容，我们开始今天的分享：层次查询（递归查询），今天的内容属于选学内容，虽然不是很复杂的内容，但是理解和应用起来较为困难，需要初学的伙伴们多加练习和思考

层次查询（hierarchical query）也常被称为递归查询，属于查询语句的一个语法块，写在WHERE条件（如果有的话）的后面，ORDER语句的前面

如果一张表中的数据行与行之间存在层级关系（hierarchical data），我们则可以使用递归查询语法来展现这种层级关系

例如在EMP表中就存在着层级关系：每一行数据MGR列中的值可以追溯其他行的EMPNO列，这种数据行间的层级还原了现实世界中的职位上下隶属

图示的EMP表中数据的层次关系就像一棵大树一样，所以这种数据的层次关系也称为树状关系，像图中KING这样的“根源”节点被称为“根节点”，而在树的最末端，像MILLER、SMITH这样的节点则被称为“叶子节点”

事实上在一个数据树中，既存在有多个根节点的现象，也存在只有根节点的现象

SQL 中的层次查询被专门设计用来展现这样的树状结构的数据

语法说明

层次查询子句写在WHERE条件（如果有的话）的后面，ORDER语句的前面

[START WITH condition]
CONNECT BY condition

START WITH 子句用以在查询中指定开始检索的根节点（可以是多行）

CONNECT BY 子句用以指示层级关系的内在关联，即递归条件

START WITH 子句既可以写在 CONNECT BY 子句的前面，也可以写在后面，还可以省略不写。如果不写 START WITH 子句，Oracle 会将每一行依次作为根节点递归检索各自的层次树（层次树也可以只有一个节点，该节点既是叶子节点也是根节点）

递归条件（由 CONNECT BY 指出）中，必须使用 PRIOR 运算符来标记父行的联结列。官方手册中写到“即便递归条件是由多个子条件复合而成的，也只须其中一条使用 PRIOR 标记即可。”需要注意的是，这里只是说只要有一条子表达式带有 PRIOR 标记即满足语法规则，但并不代表只在一个子表达式里使用了 PRIOR 标记后，Oracle 就会按照编码者想要的方式行事

进一步说明 PRIOR 标记的作用，PRIOR 是一元运算符，优先级等同于算术运算符中的+或-，它只作用于紧跟的表达式。例如：CONNECT BY PRIOR empno = mgr 表示在每一对父子行之间，父行的 empno 列值等于子行的 mgr 列值

这里需要说明的是，父行不一定总是现实意义中的上级，子行也不一定总是现实意义中的下级，父行指的是在递归查询过程中较先检索的一级，而其子行则是根据递归条件检索到的下一层级的行

事实上 PRIOR 放在算式的哪一侧都是可行的，所以 CONNECT BY mgr = PRIOR empno 与上例是完全一致的，但是 CONNECT BY empno = PRIOR mgr 则表意父行的 mgr 列与子行的 empno 列组合为联结键

SQL> SELECT e.ename, e.empno, e.mgr
  2    FROM emp e
  3   START WITH e.mgr IS NULL
  4  CONNECT BY PRIOR e.empno = e.mgr;


ENAME      EMPNO   MGR
---------- ----- -----
KING        7839 
JONES       7566  7839
SCOTT       7788  7566
ADAMS       7876  7788
FORD        7902  7566
SMITH       7369  7902
BLAKE       7698  7839
ALLEN       7499  7698
WARD        7521  7698
MARTIN      7654  7698
TURNER      7844  7698
JAMES       7900  7698
CLARK       7782  7839
MILLER      7934  7782


14 rows selected
复制

上例展示了层次查询的一个简单应用，START WITH mgr IS NULL 表示从每个 MGR 字段为空值的行开始递归，这是对现实意义中“从最高职级（具体来说就是没有上级）的人员开始查找”这种想法的解释；CONNECT BY PRIOR empno = mgr 则规定使用父行的 EMPNO 字段的值查找其子行，条件是子行的 MGR 字段值匹配该 EMPNO 值，这则是对现实意义中“上级的员工编号则是其下级人员的‘上级编号’”这种想法的代码解释

实现原理

在 SQL 语句结构中，表联结（如果有的话）最先执行，接下来按照 CONNECT BY 子句声明的条件进行递归检索，此后 WHERE 条件中其它筛选性的谓语执行，ORDER BY 语句（如果有的话）最后执行

定位到 START WITH 指示的根节点；
依据 CONNECT BY 指明的关系先找到根节点下一级的子行；
每找到一层子行，便再向下检索一层，如此递进，直至所有叶子节点（没有下层的行）被找到；
如果 WHERE 条件中还有筛选性的谓语，此时 Oracle 将独立地检查上述步骤得出的结果集中的每一行，将不符合筛选条件的行舍弃；
最后 Oracle 按照从根节点到叶子节点的顺序返回结果集，子行将排列在其父行的下面；
按照语句中规定的结果集输出的排序（如果有的话）加工结果集

LEVEL

LEVEL 伪列表示递归查询中层级的深度，根节点上 LEVEL 为1，如前所述递归查询的检索顺序，每增加一层，LEVEL 值就加一。从而 LEVEL 值相同的行表示位于同一层级（或称“兄弟层级”）

LEVEL 值最大的行一定是叶子节点，但叶子节点的 LEVEL 不一定都是最大的

SQL> SELECT e.ename, e.empno, e.mgr, LEVEL
  2    FROM scott.emp e
  3   START WITH e.mgr IS NULL
  4  CONNECT BY PRIOR e.empno = e.mgr;


ENAME      EMPNO   MGR      LEVEL
---------- ----- ----- ----------
KING        7839                1
JONES       7566  7839          2
SCOTT       7788  7566          3
ADAMS       7876  7788          4
FORD        7902  7566          3
SMITH       7369  7902          4
BLAKE       7698  7839          2
ALLEN       7499  7698          3
WARD        7521  7698          3
MARTIN      7654  7698          3
TURNER      7844  7698          3
JAMES       7900  7698          3
CLARK       7782  7839          2
MILLER      7934  7782          3


14 rows selected
复制

SIBLINGS

如前文所述，Oracle 默认将子行排在其父行的后面，此后如果还有 ORDER BY 子句的话，将会针对整个结果集执行排序，这时将不会再顾及他们的层级关系。为了不打乱原本的层级顺序，我们可以使用 ORDER SIBLINGS BY 语句，这将在保持层级顺序的前提下，仅在每个兄弟层级层面执行排序

SQL> -- ORDER BY 会“粗鲁地”打乱父子层次的顺序
SQL> SELECT e.ename, e.empno, e.mgr, LEVEL
  2    FROM scott.emp e
  3   START WITH e.mgr IS NULL
  4  CONNECT BY PRIOR e.empno = e.mgr
  5   ORDER BY e.empno DESC;


ENAME      EMPNO   MGR      LEVEL
---------- ----- ----- ----------
MILLER      7934  7782          3
FORD        7902  7566          3
JAMES       7900  7698          3
ADAMS       7876  7788          4
TURNER      7844  7698          3
KING        7839                1
SCOTT       7788  7566          3
CLARK       7782  7839          2
BLAKE       7698  7839          2
MARTIN      7654  7698          3
JONES       7566  7839          2
WARD        7521  7698          3
ALLEN       7499  7698          3
SMITH       7369  7902          4


14 rows selected


SQL> -- ORDER SIBLINGS BY 仅作用于“兄弟层次”
SQL> SELECT e.ename, e.empno, e.mgr, LEVEL
  2    FROM scott.emp e
  3   START WITH e.mgr IS NULL
  4  CONNECT BY PRIOR e.empno = e.mgr
  5   ORDER SIBLINGS BY e.empno DESC;


ENAME      EMPNO   MGR      LEVEL
---------- ----- ----- ----------
KING        7839                1
CLARK       7782  7839          2
MILLER      7934  7782          3
BLAKE       7698  7839          2
JAMES       7900  7698          3
TURNER      7844  7698          3
MARTIN      7654  7698          3
WARD        7521  7698          3
ALLEN       7499  7698          3
JONES       7566  7839          2
FORD        7902  7566          3
SMITH       7369  7902          4
SCOTT       7788  7566          3
ADAMS       7876  7788          4


14 rows selected
复制

制造数列

在一些应用场景中，需要使用某些临时的序列，层次查询可以在这些场景中便捷地使用

SQL> SELECT LEVEL FROM dual CONNECT BY LEVEL <= 10;


     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10


10 rows selected


SQL> SELECT LEVEL FROM dual WHERE LEVEL > 5 CONNECT BY LEVEL <= 10;


     LEVEL
----------
         6
         7
         8
         9
        10


SQL> SELECT chr(96 + LEVEL) letter FROM dual CONNECT BY LEVEL <= 26;


LETTER
------
a
b
c
d
e
f
g
h
i
j
k
l
m
n
o
p
q
r
s
t
u
v
w
x
y
z


26 rows selected
复制

注：上例中的内置函数 CHR，可以将十进制的 ASCII 码转换成对应的字符

oracle

文章转载自SQL干货分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle PL/SQL 基础教程（8）复杂查询（五）

评论