ORACLE CONNECT_BY_ROOT,CONNECT_BY_ISLEAF等在GaussDB(DWS)上实现
【摘要】 最近在项目中遇到ORACLE START WITH ... CONNECT BY PRIOR这类型的语句比较多,这类型的语句没办法直接在DWS上直接执行,需要通过DSC工具转化为GaussDB(DWS)的语法。DSC能够处理绝大部分这类型的SQL ,但是仍然有部分特殊的写法,如CONNECT_BY_ROOT 字段、CONNECT_BY_LEAF、SYS_CONNECT_BY_PATH、PRI...
最近在项目中遇到ORACLE START WITH ... CONNECT BY PRIOR这类型的语句比较多,这类型的语句没办法直接在DWS上直接执行,需要通过DSC工具转化为GaussDB(DWS)的语法。DSC能够处理绝大部分这类型的SQL ,但是仍然有部分特殊的写法,如CONNECT_BY_ROOT 字段、CONNECT_BY_LEAF、SYS_CONNECT_BY_PATH、PRIOR columnname ,DSC没办法识别并成功转化,下面来简单介绍一下我遇到的情况。
CONNECT BY 相关字段在Gauss(DWS)上实现
项目中遇到需要转换的字段主要包括以下几个
CONNECT_BY_ROOT 字段、CONNECT_BY_ISLEAF、SYS_CONNECT_BY_PATH、PRIOR columnname
在ORACLE上的查询结果
首先生成数据
CREATE TABLE T_TREE (ID NUMBER PRIMARY KEY, FATHER_ID NUMBER, NAME VARCHAR2(30));
INSERT INTO T_TREE VALUES (1, 0, 'A');
INSERT INTO T_TREE VALUES (2, 1, 'BC');
INSERT INTO T_TREE VALUES (3, 1, 'DE');
INSERT INTO T_TREE VALUES (4, 1, 'FG');
INSERT INTO T_TREE VALUES (5, 2, 'HIJ');
INSERT INTO T_TREE VALUES (6, 4, 'KLM');
INSERT INTO T_TREE VALUES (7, 6, 'NOPQ');
INSERT INTO T_TREE VALUES (9, 8, 'RST');
INSERT INTO T_TREE VALUES (11, 10, 'UVW');
2. 查询相关字段信息
SELECT ID, FATHER_ID, NAME
,CONNECT_BY_ROOT NAME ROOT_NAME
,CONNECT_BY_ISLEAF LEAF
,PRIOR NAME
,CAST(SYS_CONNECT_BY_PATH(NAME, '/') AS VARCHAR(20)) as PATH
,LEVEL
FROM T_TREE
START WITH FATHER_ID IN (0,10)
CONNECT BY PRIOR ID = FATHER_ID
ORDER BY ID,FATHER_ID
;
结果如下
简单解析一下每个字段的意思
CONNECT_BY_ROOT目的是找到CONNECT BY迭代展开时的根条目,比如上面的A是下面条目的根条目。
CONNECT_BY_ISLEAF目的是指出该条目是否是叶子条目,比如上面ID为3的,没有以ID=3作为父节点的条目了,所以它就是叶子条目
PRIOR NAME目的是找到上一节点的条目的NAME,若上一条目是根的话就为空串
SYS_CONNECT_BY_PATH(NAME,'/')目的是将迭代展开的路径按照函数指定的分隔符打印出来。
在Gauss(DWS)上的查询结果
在了解ORACLE的结果的具体意思后,使用DSC转化SQL,并对其无法转化的字段改写
WITH RECURSIVE T_TREE_GAUSS AS (
SELECT ID,FATHER_ID,NAME
,NAME ROOT_NAME
,'/'||C.NAME as PATH
,cast('' as varchar(30)) AS PRIORNAME
,0 AS LEVEL
FROM T_TREE C
WHERE FATHER_ID IN (0,10)
UNION ALL
SELECT A.ID,A.FATHER_ID,A.NAME
,B.ROOT_NAME
,CAST(B.path||'/'||A.NAME AS VARCHAR(30))
,B.NAME
,LEVEL + 1
FROM T_TREE A
INNER JOIN T_TREE_GAUSS B
ON A.FATHER_ID = B.ID
)
SELECT DISTINCT T1.ID,T1.FATHER_ID,T1.NAME
,T1.ROOT_NAME
,CASE WHEN T2.ID IS NULL THEN '1' ELSE '0' END AS LEAF
,T1.PRIORNAME
,T1.PATH
,T1.LEVEL
FROM T_TREE_GAUSS T1
LEFT JOIN T_TREE_GAUSS T2
ON T1.ID = T2.FATHER_ID
ORDER BY T1.ID,T1.FATHER_ID
;
Gauss(DWS)的SQL是按照ORACLE字段的意思改写过来,其中CONNECT_BY_ISLEAF,我思考很久没有想到直接在WITH RECURSIVE XX AS这里面直接实现,而是通过关联得到,主要逻辑是不是父节点的那些条目都是叶子,T1.ID = T2.FATHER_ID,关联不上的那些都是叶子节点。
总结
上面是ORACLE CONNECT BY ... START WITH PRIOR的部分字段在Gauss(DWS)上的实现,主要理解了ORACLE字段的逻辑以后,就能够比较简单地实现了。笔者之前没有太多接触ORACLE,所以实现这些字段的时候还是花了些时间。希望对读者后面使用Gauss(DWS)有所帮助。
【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
作者其他文章
评论(0)