DM层次查询

一、前言

　　通常有些情况需要查询获得某单位的上级或下级的信息，这种查询关系属于层级递归查询（又称：树状结构查询），该技术涉及根节点、父节点、子节点、叶子节点等特点运用，帮助我们很快定位出一个部门的组织层级关系，按预定的条件展示出来。

二、层次查询实现技术

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子句

四、归纳总结
　　connect by层级查询子句中未指定start with定位点成员，默认是以表中所有的节点为根节点进行先根遍历再进行层次查询。
　　with…as短句实现递归查询比较灵活，提高SQL代码的可读性，同时也提升了执行性能（利用临时表暂存功能）。
　　总体来说，connect by 比with as 在实现层级查询的时候，书写SQL较为简易，附加的操作符、函数、伪列丰富，可直接使用。

原文链接： DM层次查询 | 达梦技术社区 (dameng.com) 

原文链接： https://eco.dameng.com/community/training/16179ee13b51645e04934b5f43e677ee