一、前言
通常有些情况需要查询获得某某的上级领导或下属员工的信息，这种查询关系属于层级递归查询（又称：树状结构查询），该技术涉及根节点、父节点、子节点、叶子节点等特点运用，帮助我们很快定位出一个部门的组织层级关系，按预定的条件展示出来。
二、层次查询实现技术
2.1 层次查询子句
层次查询中利用层次伪列、层次函数或层次操作符来明确层次查询结果中的相应层次信息。

层次伪列
1．LEVEL 该伪列表示当前元组在层次数据形成的树结构中的层数。LEVEL 的初始值为 1，即层 次数据的根节点数据的 LEVEL 值为 1，之后其子孙节点的 LEVEL 依次递增。
2．CONNECT_BY_ISLEAF 该伪列表示当前元组在层次数据形成的树结构中是否是叶节点(即该元组根据连接条件不存在子结点)。是叶节点时为 1，否则为 0。
3．CONNECT_BY_ISCYCLE 该伪列表示当前元组是否会将层次数据形成环，该伪列只有在层次查询子句中表明 NOCYCLE关键字时才有意义。如果元组的存在会导致层次数据形成环，该伪列值为 1，否 则为 0。
层次操作符
1．PRIOR 用在层次查询子句中，有PRIOR操作符修饰的列，是逻辑表达式中的父节点。
PRIOR操作符可以出现在查询项、WHERE条件、GROUP BY子句、集函数参数中，表示父层记录对应的值。
2．CONNECT_BY_ROOT 查询在层次查询结果中根节点的某列的值。
层次函数
SYS_CONNECT_BY_PATH(col_name,char)
从根节点到当前节点路径上所有节点名为col_name的某列的值，中间使用char指定的符号分隔。
如：/boss/manager/employee
层次查询层内排序
ORDER SIBLINGS BY <order_by_list> 指定层次查询中相同层次数据返回的顺序。 在层次查询中使用 ORDER SIBLINGS BY，必须与CONNECT BY 一起配合使用。 但是，ORDER SIBLINGSBY不能和GROUP BY一起使用。
层次查询限制

1. START WITH子句中不能使用层次查询的所有伪列、层次查询函数、操作符；
2. ORDER SIBLINGS BY 子句中不能使用层次查询的所有伪列、层次查询函数、操作符、ROWNUM 以及子查询；
3. 层次查询子句不能使用伪列 CONNECT_BY_ISLEAF、CONNECT_BY_ISCYCLE，SYS_CONNECT_BY_PATH 伪函数和 CONNECT_BY_ROOT 操作符；
4. JOIN ON 子句中不允许出现层次查询的所有伪列、层次查询函数；
5. PRIOR、CONNECT_BY_ROOT 操作符后以及 SYS_CONNECT_BY_PATH 第一个参数不能使用层次查询的所有伪列、层次查询函数、操作符、ROWNUM 以及子查询。但SYS_CONNECT_BY_PATH的第一个参数允许出现 LEVEL 伪列且第二个参数必须是常量字符串，CONNECT_BY_ROOT LEVEL 也被允许；
6. 函数 SYS_CONNECT_BY_PATH 的最大返回长度为 8188，超长就会报错。函数SYS_CONNECT_BY_PATH 在一个查询语句中最多使用个数为 64；
7. INI参数CNNTB_MAX_LEVEL表示支持层次查询的最大层次，默认为20000。该参数的有效取值为[1, 100000]。
   2.2 递归with CTE子句
   
   
   递归with cte子句
   <递归 with cte 子句> ::= <公用表表达式的名称> (<列名>{,<列名>}) AS (<定位点成员> UNION ALL
   <递归成员>)
   定位点成员：任何不包含<公用表表达式的名称>的SELECT查询语句，可以UNION ALL、UNION、INTERSECT 或 MINUS。
   定位点成员的查询结果集是递归成员迭代的基础。可以把它理解成递归查询的起点节点的数据行。
   递归成员：引用<公用表表达式的名称>的SELECT查询语句。
   递归成员通过引用自身<公用表表达式的名称>反复迭代执行，下一次迭代的数据基于上一次迭代的查询结果，当且仅当本次迭代结果为空集时才终止迭代。
   cte 查询语句： 支持任意类型的SELECT查询语句，但通常引用<公用表表达式的名称>达到目标结果才更有意义。
   注意：与递归with相关参数
   CTE_MAXRECURSION 指定递归CTE迭代层次，取值范围[1，ULINT_MAX]，默认100。
   CTE_OPT_FLAG 指定递归WITH相关子查询是否转换为 WITH FUNCTION 优化，取值0或1，默认1。
   递归成员限制
   不能包含DISTINCT或GROUP BY；
   不能包含集函数，但支持分析函数；
   <公用表表达式的名称>不能在<递归with cte 子句>中使用；
   <公用表表达式的名称>不能作为<递归成员>中外连接OUTER JOIN 的右表；
   <递归成员>中列的数据类型必须与定位点成员中相应列的数据类型兼容；
   <公用表表达式的名称>在定位点成员中不能出现；
   <公用表表达式的名称>在递归成员中有且只能引用一次。
   三、实战案例
   实验环境：
   数据库版本：1-1-126-20.09.04-126608-ENT
   操作系统：CentOS Linux release 7.7.1908
   (Core) SCOTT模式：从Oracle示例库迁移而来的数据
   3.1 connect by层次查询子句实现层级查询
   要求：以员工号为7839的员工数据行开始从上往下递归查询连接，按员工员以升序方式显示出直属领导与下属员工信息，并显示全路径结构。
   SELECT
   a.mgr "直属领导ID" ,
   PRIOR a.ename "直属领导名字" ,
   a.empno "下属员工ID" ,
   a.ename "下属员工名字" ,
   SYS_CONNECT_BY_PATH(a.ename, '/') "层次路径",
   LEVEL "层级"
   FROM scott.emp a
   CONNECT BY PRIOR a.empno = a.mgr
   START WITH a.empno = 7839
   ORDER SIBLINGS BY a.empno;
   
   3.2 WITH AS子句实现递归查询
   WITH ee(mgr,empno,ename,vpath,lvl) AS (
   SELECT mgr,empno,ename,'/'||ename vpath,1 lvl FROM scott.emp WHERE empno = 7839
   UNION ALL
   SELECT a.mgr,a.empno,a.ename, vpath||'/'||a.ename vpath ,b.lvl + 1
   FROM scott.emp a join ee b on a.mgr = b.empno
   )
   SELECT d.mgr "直属领导ID",
   c.ename "直属领导名字" ,
   d.empno "下属员工ID",
   d.ename "下属员工名字",
   d.vpath "层次路径",
   d.lvl "层级"
   FROM ee d left join scott.emp c on d.mgr = c.empno;
   
   四、归纳总结
   connect by层级查询子句中未指定start with定位点成员，默认是以表中所有的节点为根节点进行先根遍历再进行层次查询。
   with…as短句实现递归查询比较灵活，提高SQL代码的可读性，同时也提升了执行性能（利用临时表暂存功能）。
   总体来说，connect by 比with as 在实现层级查询的时候，书写SQL较为简易，附加的操作符、函数、伪列丰富，可直接使用。