GBase 8s中的start with connect by用法_start with connect by nocycle

如果表中存在层次数据，则可以使用层次化查询子句查询出表中行记录之间的层次关系
基本语法：

[ START WITH CONDITION1 ]
CONNECT BY [ NOCYCLE ] CONDITION2
1
2

示例：

select id,parentid,partname, level
from tabpart
start with id = 11
connect by nocycle prior id = parentid;
1
2
3
4

表中存在层次数据
数据之间的层次关系即父子关系，通过表中列与列间的关系来描述，如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;
1
2
3
4
5

结果：

         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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

分析
层次查询执行逻辑:
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;
1
2
3
4
5

结果：

         id    parentid       level

       1211         121           1
        121          12           2
         12           1           3
          1           0           4
1
2
3
4
5
6

分析
层次查询执行逻辑:
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 
1

prior 和子列在一起，表示寻找它的子孙，即自顶向下；

-- 向下
select id,parentid, level
from tabpart
start with id = 12
connect by prior id = parentid;
1
2
3
4
5

prior 和父列在一起，表示开始寻找它的爸爸，即自下向上;

-- 向上
select id,parentid, level
from tabpart
start with id = 12
connect by id =  prior parentid;
1
2
3
4
5

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;
1
2
3
4
5

将报错：

26079: CONNECT BY 查询已导致循环。
1

加上NOCYCLE

select id,parentid, level, CONNECT_BY_ISCYCLE
from tabpart
start with parentid = 0
connect by nocycle prior id = parentid;
1
2
3
4

结果：

         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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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;
1
2
3
4

结果：

         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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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;
1
2
3
4

结果：

         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 /总部/华北区/北京
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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;
1
2
3
4
5

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;
1
2
3
4

结果：

         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 总部
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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;
1
2
3
4
5

结果：

         id    parentid partnum                    level

          1           0 总部                           4
         12           1 华南区                         3
        121          12 广东                           2
       1211         121 广州                           1
1
2
3
4
5
6

自下而上，使用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
);
1
2
3
4
5
6
7
8

结果：

(expression)  总部 -> 华南区 -> 广东 -> 广州
1

涉及到的表结构及语句

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22