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、PRIOR columnname ，DSC没办法识别并成功转化，下面来简单介绍一下我遇到的情况。

CONNECT BY 相关字段在Gauss(DWS)上实现

项目中遇到需要转换的字段主要包括以下几个

CONNECT_BY_ROOT 字段、CONNECT_BY_ISLEAF、SYS_CONNECT_BY_PATH、PRIOR columnname 

在ORACLE上的查询结果

1. 首先生成数据
   

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)有所帮助。