如果表中存在层次数据,则可以使用层次化查询子句查询出表中行记录之间的层次关系
基本语法:
[ START WITH CONDITION1 ]
CONNECT BY [ NOCYCLE ] CONDITION2
示例:
select id,parentid,partname, level
from tabpart
start with id = 11
connect by nocycle prior id = parentid;
表中存在层次数据
数据之间的层次关系即父子关系,通过表中列与列间的关系来描述,如tabpart表中的id和parentid。id表示部门编号,parentid表示部门归属的更高层部门编号,在表的每一行中都有一个表示父节点的parentid(除根节点外),通过每个节点的父节点,就可以确定整个树结构。
CONNECT BY [ NOCYCLE ] CONDITION2 层次子句作用
CONDITION2 [PRIOR expr = expr] : 指定层次结构中父节点与子节点之之间的关系。
CONDITION2 中的 一元运算符 PRIORY 必须放置在连接关系的两列中某一个的前面。在连接关系中,除了可以使用列名外,还允许使用列表达式。
1.START WITH
start with 子句为可选项,用来标识哪行作为查找树型结构的第一行(即根节点,可指定多个根节点)。若该子句被省略,则表示所有满足查询条件的行作为根节点。
2.关于PRIOR PRIOR置于运算符前后的位置,决定着查询时的检索顺序。
2.1 从根节点自顶向下
-- sql 1
select id,parentid, level
from tabpart
start with parentid = 0
connect by prior id = parentid;
结果:
id parentid level
1 0 1
13 1 2
12 1 2
123 12 3
122 12 3
121 12 3
1213 121 4
1212 121 4
1211 121 4
11 1 2
113 11 3
112 11 3
111 11 3
分析
层次查询执行逻辑:
a.确定上一行(相对于步骤b中的当前行),若start with 子句存在,则以该语句确定的行为上一行,若不存在则将所有的数据行视为上一行。
b.从上一行出发,扫描除该行之外所有数据行。
c.匹配条件 prior id = parentid
注意 一元运算符 prior,意思是之前的,指上一行
当前行定义:步骤b中扫描得到的所有行中的某一行
匹配条件含义:当前行字段 parentid 的值等于上一行字段id中的值,若满足则取出该行,并将level + 1,匹配完所有行记录后,将满足条件的行作为上一行,执行步骤 b,c。直到所有行匹配结束。
2.2 从根节点自底向上
--sql 2
select id,parentid, level
from tabpart
start with id = 1211
connect by prior parentid = id;
结果:
id parentid level
1211 121 1
121 12 2
12 1 3
1 0 4
分析
层次查询执行逻辑:
a.确定上一行(相对于步骤b中的当前行),若start with 子句存在,则以该语句确定的行为上一行,若不存在则将所有的数据行视为上一行。
b.从上一行出发,扫描除该行之外所有数据行。
c.匹配条件 parentid = id
注意 一元运算符 prior,意思是之前的,指上一行
当前行定义:步骤b中扫描得到的所有行中的某一行
匹配条件含义:当前行字段 id 的值等于上一行字段 parentid 中的值,若满足则取出该行,并将 level + 1,匹配完所有行记录后,将满足条件的行作为上一行,执行步骤 b,c。直到所有行匹配结束。
自顶向下,自下向上口诀:
start with child_id = 10 connect by [prior] child_id = [prior] parent_id
prior 和子列在一起,表示寻找它的子孙,即自顶向下;
-- 向下
select id,parentid, level
from tabpart
start with id = 12
connect by prior id = parentid;
prior 和父列在一起,表示开始寻找它的爸爸,即自下向上;
-- 向上
select id,parentid, level
from tabpart
start with id = 12
connect by id = prior parentid;
3.特定于层级查询的 SQL 语法
3.1 LEVEL伪列
指示该层级之内递归查询的哪一步骤返回了行。即从start with的条件开始为第一层,每一迭代加一层。示例如上。
3.2 CONNECT_BY_ISCYCLE 伪列
指示一行是否有一个还是其祖先的孩子。存在循环时,当出现该情况时,connect by子句应该加上NOCYCLE。
示例:
表中增加一行数据,使符合循环的要求,执行sql 1
-- sql 1
select id,parentid, level
from tabpart
start with parentid = 0
connect by prior id = parentid;
将报错:
26079: CONNECT BY 查询已导致循环。
加上NOCYCLE
select id,parentid, level, CONNECT_BY_ISCYCLE
from tabpart
start with parentid = 0
connect by nocycle prior id = parentid;
结果:
id parentid level connect_by_iscycle
1 0 1 0
13 1 2 0
12 1 2 0
123 12 3 0
122 12 3 0
121 12 3 0
1213 121 4 0
1212 121 4 0
1211 121 4 0
0 1211 5 1
11 1 2 0
113 11 3 0
112 11 3 0
111 11 3 0
3.3 CONNECT_BY_ISLEAF 伪列
指示一行在查询返回的行之中是否有任何的孩子。如果是leaf(叶子),返回1,否则返回0。
示例:
select id,parentid, level, CONNECT_BY_ISLEAF
from tabpart
start with parentid = 0
connect by prior id = parentid;
结果:
id parentid level connect_by_isleaf
1 0 1 0
13 1 2 1
12 1 2 0
123 12 3 1
122 12 3 1
121 12 3 0
1213 121 4 1
1212 121 4 1
1211 121 4 0
0 1211 5 0
11 1 2 0
113 11 3 1
112 11 3 1
111 11 3 1
3.4 SYS_CONNECT_BY_PATH 函数
可构建和返回一字符串,该字符串表示从指定的行到层级的根的路径。类似于wm_concat函数,按层次顺序拼接字段。
示例:
select id,parentid, level, SYS_CONNECT_BY_PATH(partname,'/')
from tabpart
start with parentid = 0
connect by nocycle prior id = parentid;
结果:
id parentid level expression_1
1 0 1 /总部
13 1 2 /总部/华东区
12 1 2 /总部/华南区
123 12 3 /总部/华南区/海南
122 12 3 /总部/华南区/广西
121 12 3 /总部/华南区/广东
1213 121 4 /总部/华南区/广东/佛山
1212 121 4 /总部/华南区/广东/深圳
1211 121 4 /总部/华南区/广东/广州
0 1211 5 /总部/华南区/广东/广州/天河
11 1 2 /总部/华北区
113 11 3 /总部/华北区/天津
112 11 3 /总部/华北区/河北
111 11 3 /总部/华北区/北京
3.5 ORDER SIBLINGS BY中的SIBLINGS关键字
对返回的每个级别的同一父母的兄弟行进行排序。
示例:
select id,parentid, level, partname
from tabpart
start with id = 1
connect by nocycle prior id = parentid
ORDER SIBLINGS BY id;
3.6 CONNECT_BY_ROOT 运算符
为其运算对象的根祖先返回一表达式。
示例:
select id,parentid, level, CONNECT_BY_ROOT partname
from tabpart
start with parentid = 0
connect by nocycle prior id = parentid;
结果:
id parentid level partname
1 0 1 总部
13 1 2 总部
12 1 2 总部
123 12 3 总部
122 12 3 总部
121 12 3 总部
1213 121 4 总部
1212 121 4 总部
1211 121 4 总部
0 1211 5 总部
11 1 2 总部
113 11 3 总部
112 11 3 总部
111 11 3 总部
3.7 PRIOR 运算符
引用从前一递归步骤返回的值(此处的“步骤”是指该递归查询的一次迭代)。
参见第二部分。
4. 示例用法
4.1 递归查询整个路径并行转列组合成字符串(自下而上)
递归查询路径
select id,parentid,partname, level
from tabpart
start with id = 1212
connect by nocycle id = prior parentid
order by level desc;
结果:
id parentid partnum level
1 0 总部 4
12 1 华南区 3
121 12 广东 2
1211 121 广州 1
自下而上,使用SYS_CONNECT_BY_PATH,获取的结果并不是需要的自上而下的顺序。这里可以使用wm_concat来拼接。
select replace(wm_concat(partname),',',' -> ')
from (
select id,parentid, partname, level
from tabpart
start with id = 1211
connect by nocycle id = prior parentid
order by level desc
);
结果:文章来源:https://www.toymoban.com/news/detail-481203.html
(expression) 总部 -> 华南区 -> 广东 -> 广州
涉及到的表结构及语句文章来源地址https://www.toymoban.com/news/detail-481203.html
drop table if exists tabpart;
create table tabpart(id int, parentid int, partname varchar(200),primary key(id));
insert into tabpart values(1,0,'总部');
insert into tabpart values(11,1,'华北区');
insert into tabpart values(12,1,'华南区');
insert into tabpart values(13,1,'华东区');
insert into tabpart values(111,11,'北京');
insert into tabpart values(112,11,'河北');
insert into tabpart values(113,11,'天津');
insert into tabpart values(121,12,'广东');
insert into tabpart values(122,12,'广西');
insert into tabpart values(123,12,'海南');
insert into tabpart values(1211,121,'广州');
insert into tabpart values(1212,121,'深圳');
insert into tabpart values(1213,121,'佛山');
-- 增加一行,用于循环
insert into tabpart values(0,1211,'天河区');
-- 修改为正确值
update tabpart set id = 12111 where id = 0;
到了这里,关于GBase 8s中的start with connect by用法的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!