GBase 8s中的start with connect by用法
如果表中存在层次数据，则可以使用层次化查询子句查询出表中行记录之间的层次关系
基本语法：
1 2 | [ START WITH CONDITION1 ]CONNECT BY [ NOCYCLE ] CONDITION2 |
示例：
1 2 3 4 | select id,parentid,partname, levelfrom tabpartstart with id = 11connect 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 从根节点自顶向下
1 2 3 4 5 | -- sql 1select id,parentid, levelfrom tabpartstart with parentid = 0connect by prior id = parentid; |
结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | id parentid level 1 0 1 13 1 2 12 1 2 123 12 3 122 12 3 121 12 31213 121 41212 121 41211 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 从根节点自底向上
1 2 3 4 5 | --sql 2select id,parentid, levelfrom tabpartstart with id = 1211connect by prior parentid = id; |
结果：
1 2 3 4 5 6 | id parentid level1211 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。直到所有行匹配结束。
自顶向下，自下向上口诀：
1 | start with child_id = 10 connect by [prior] child_id = [prior] parent_id |
prior 和子列在一起，表示寻找它的子孙，即自顶向下；
1 2 3 4 5 | -- 向下select id,parentid, levelfrom tabpartstart with id = 12connect by prior id = parentid; |
prior 和父列在一起，表示开始寻找它的爸爸，即自下向上;
1 2 3 4 5 | -- 向上select id,parentid, levelfrom tabpartstart with id = 12connect by id = prior parentid; |
3.特定于层级查询的 SQL 语法
3.1 LEVEL伪列
指示该层级之内递归查询的哪一步骤返回了行。即从start with的条件开始为第一层，每一迭代加一层。示例如上。
3.2 CONNECT_BY_ISCYCLE 伪列
指示一行是否有一个还是其祖先的孩子。存在循环时，当出现该情况时，connect by子句应该加上NOCYCLE。
示例：
表中增加一行数据，使符合循环的要求，执行sql 1
1 2 3 4 5 | -- sql 1select id,parentid, levelfrom tabpartstart with parentid = 0connect by prior id = parentid; |
将报错：
1 | 26079: CONNECT BY 查询已导致循环。 |
加上NOCYCLE
1 2 3 4 | select id,parentid, level, CONNECT_BY_ISCYCLEfrom tabpartstart with parentid = 0connect by nocycle prior id = parentid; |
结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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 01213 121 4 01212 121 4 01211 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。
示例：
1 2 3 4 | select id,parentid, level, CONNECT_BY_ISLEAFfrom tabpartstart with parentid = 0connect by prior id = parentid; |
结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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 01213 121 4 11212 121 4 11211 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函数，按层次顺序拼接字段。
示例：
1 2 3 4 | select id,parentid, level, SYS_CONNECT_BY_PATH(partname,'/')from tabpartstart with parentid = 0connect by nocycle prior id = parentid; |
结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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关键字
对返回的每个级别的同一父母的兄弟行进行排序。
示例：
1 2 3 4 5 | select id,parentid, level, partnamefrom tabpartstart with id = 1connect by nocycle prior id = parentidORDER SIBLINGS BY id; |
3.6 CONNECT_BY_ROOT 运算符
为其运算对象的根祖先返回一表达式。
示例：
1 2 3 4 | select id,parentid, level, CONNECT_BY_ROOT partnamefrom tabpartstart with parentid = 0connect by nocycle prior id = parentid; |
结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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 递归查询整个路径并行转列组合成字符串(自下而上)
递归查询路径
1 2 3 4 5 | select id,parentid,partname, levelfrom tabpartstart with id = 1212connect by nocycle id = prior parentidorder by level desc; |
结果：
1 2 3 4 5 6 | id parentid partnum level 1 0 总部 4 12 1 华南区 3 121 12 广东 21211 121 广州 1 |
自下而上，使用SYS_CONNECT_BY_PATH，获取的结果并不是需要的自上而下的顺序。这里可以使用wm_concat来拼接。
1 2 3 4 5 6 7 8 | 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 | (expression) 总部 -> 华南区 -> 广东 -> 广州 |
涉及到的表结构及语句
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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; |