达梦数据查询编码_【干货分享】达梦数据库层次查询子句

点击蓝字关注我们下周就是新年了，在此恭祝大家新春快乐，鼠年吉祥！祝大家2020年有“鼠”不尽的快乐，“鼠”不尽的收获，“鼠”不尽的钞票，“鼠”不尽的笑容，“鼠”不尽的喜气，“鼠”不尽的激情，“鼠”不尽的健康，“鼠”不尽的幸福！今天我们分享一下达梦数据库的层级查询子句，层级关系在实际业务中应用非常广泛，学会了层级查询将给我们工作带来很多便利。

本章的测试环境：

操作系统: 中标麒麟7  64位

数据库版本：达梦8.1

达梦数据库客户端：DM管理工具

相关关键字：层次查询， connect by

层次查询子句

层次查询子句用于层次查询，得到数据间的层次关系。在使用层次查询子句时， 可以使用层次查询相关的伪列、函数或操作符来明确层次查询结果中的相应层次信息。

语法介绍

语法格式：

::=

CONNECT BY [NOCYCLE][ START WITH ]|

START WITHCONNECT BY [NOCYCLE]

::=

::=

参数：

1．逻辑表达式，指明层次数据间的层次连接关系；

2．逻辑表达式，指明选择层次数据根数据的条件；

3．NOCYCLE 关键字用于指定数据导致环的处理方式，如果在层次查询子句中指定NOCYCLE关键字，会忽略导致环元组的儿子数据。否则，返回错误。

相关伪列

在使用层次查询子句时，可以通过相关的伪列来明确数据的层次信息。层次查询相关的伪列有：

LEVEL

该伪列表示当前元组在层次数据形成的树结构中的层数。LEVEL 的初始值为 1，即层次数据的根节点数据的 LEVEL 值为 1，之后其子孙节点的 LEVEL 依次递增。

CONNECT_BY_ISLEAF

该伪列表示当前元组在层次数据形成的树结构中是否是叶节点(即该元组根据连接条件不存在子结点)。是叶节点时为 1，否则为 0。

CONNECT_BY_ISCYCLE

该伪列表示当前元组是否会将层次数据形成环，该伪列只有在层次查询子句中表明 NOCYCLE 关键字时才有意义。如果元组的存在会导致层次数据形成环，该伪列值为 1，否则为 0。

相关操作符

PRIOR

PRIOR 操作符主要使用在层次查询子句中，指明 PRIOR 之后的参数为逻辑表达式中的父节点。PRIOR 专门用于 CONNECT BY [NOCYCLE] 的中。

例如：CONNECT BY NOCYCLE PRIOR DEPTNO = HIGH_DEPTNO;  -- DEPTNO 为父节点。下一条记录的 HIGH_DEPTNO 等于前一条记录的 DEPTNO。

CONNECT_BY_ROOT

该操作符作为查询项，查询在层次查询结果中根节点的某列的值。

相关函数

SYS_CONNECT_BY_PATH(col_name, char)

该函数得到从根节点到当前节点路径上所有节点名为 col_name 的某列的值，之间用 char 指明的字符分隔开。此函数是常用的层级关系函数，详细使用见后面示例部分。

层内排序

::= ORDER SIBLINGS BY < order_by_list>

order siblings by 用于指定层次查询中相同层次数据返回的顺序。在层次查询中使用 order siblings by，必须与 connect by 一起配合使用。但是，order siblings by 不能和 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]。

示例

这里先创建部门DEPARMENT样例表(表包含三个字段，分别为部门名称、部门编号、上级部门编号)，并插入样例数据：create table other.department

(

deptname varchar(50) null,

deptno integer null,

high_deptno integer null

);

insert into other.department(deptname,deptno,high_deptno)

values('总公司',1,null);

insert into other.department(deptname,deptno,high_deptno)

values('服务部',101,1);

insert into other.department(deptname,deptno,high_deptno)

values('财务部',102,1);

insert into other.department(deptname,deptno,high_deptno)

values('网络服务部',10101,101);

insert into other.department(deptname,deptno,high_deptno)

values('读者服务部',10102,101);

insert into other.department(deptname,deptno,high_deptno)

values('企业服务部',10103,101);

insert into other.department(deptname,deptno,high_deptno)

values('书籍借阅服务部',1010201,10102);

insert into other.department(deptname,deptno,high_deptno)

values('书籍阅览服务部',10100202,10102);

insert into other.department(deptname,deptno,high_deptno)

values('品牌部',103,1);

insert into other.department(deptname,deptno,high_deptno)

values('培训部',104,1);

insert into other.department(deptname,deptno,high_deptno)

values('事业部',106,1);

insert into other.department(deptname,deptno,high_deptno)

values('研发部',10601,106);

insert into other.department(deptname,deptno,high_deptno)

values('上海研发中心',1060101,10601);

insert into other.department(deptname,deptno,high_deptno)

values('武汉研发中心',1060102,10601);

insert into other.department(deptname,deptno,high_deptno)

values('交付部',10602,106);

commit;

插入数据后可以看出，基础部门信息表中包含了部门的上级部门编号，但是从表中数据不容易直接看出每个部门的直属关系，原始数据展示如下：

①以查询deparment样例表中总公司所有部门层级关系，并以部门编号排序为例。查询sql样例如下：

selectlevel,

connect_by_isleaf isleaf,

connect_by_iscycle iscycle,

connect_by_rootdeptnameasrootdeptname,

sys_connect_by_path(deptname,'/')high_deptnamedesc,

deptname

fromother.department

connectbypriordeptno = high_deptno

startwithdeptno = 1

ordersiblingsbydeptno;

从输出结果可以准确看出部门的层级来源，及部门的层级数。输出结果展示如下(依次为部门层级数、是否是叶子节点、是否形成环、根部门节点名称，部门层级节点描述，部门名称)：

②以仅查询“事业部”的层级关系为例，只需要将上述查询中的startwithdeptno = 1替换为startwithdeptname ='事业部'，就是事业部的部门层级关系。查询sql样例如下：

selectlevel,

connect_by_isleaf isleaf,

connect_by_iscycle iscycle,

connect_by_rootdeptnameasrootdeptname,

sys_connect_by_path(deptname,'/')high_deptnamedesc,

deptname

fromother.department

connectbypriordeptno = high_deptno

startwithdeptname ='事业部'

ordersiblingsbydeptno;

从输出结果可以准确看出事业部的部门层级数据。这里可以看出，startwith的条件就是层级关系的起点。输出结果展示如下(依次为部门层级数、是否是叶子节点、是否形成环、根部门节点名称，部门层级节点描述，部门名称)：

③当我们只知道下级部门，想查找上级部门关系时可以在样例1中增加where条件来查询某个子部门的上级关系。以查询“上海研发中心”的上级部门关系为例，查询sql样例如下：

selectlevel,

connect_by_isleaf isleaf,

connect_by_rootdeptnameasrootdeptname,

sys_connect_by_path(deptname,'/')high_deptnamedesc,

deptname

fromother.department

wheredeptname ='上海研发中心'

connectbypriordeptno = high_deptno

startwithdeptno = 1;

从输出结果可以准确看出上海研发中心的部门层级数据。输出结果展示如下：

好了，本次层次查询就介绍到这里，是不是觉得方便了很多~ 有时候如何查找上级目录也会难倒一个程序猿：)，希望本次分享能给大家带来帮助！祝大家新年快乐达梦大数据

● 扫码关注我们吧