opengauss适配Oracle的start with Connect By Prior

示例语句：

oracle语句：

SELECT ROWNUM, T.*
                      FROM (SELECT C.*,
                                   CASE
                                     WHEN C.PARENT_CODE IS NOT NULL THEN
                                      C.NURSING_PLAN_ID || '_' || C.PARENT_CODE
                                   END AS PARENT_CODE_NEW,
                                   C.NURSING_PLAN_ID || '_' || C.NURSING_PLAN_SUB_ID AS SUB_ID_NEW
                              FROM JHNIS_NURSING_PLAN_ILLNESS B
                              JOIN JHNIS_NURSING_PLAN_DICT C ON B.PLAN_ILL_ID = C.PLAN_ILL_ID
                                                            AND B.HOSPITAL_NO = C.HOSPITAL_NO
                             WHERE B.HOSPITAL_NO = '40068980X4') T
                     START WITH T.PARENT_CODE_NEW IS NULL
                    CONNECT BY PRIOR T.SUB_ID_NEW = T.PARENT_CODE_NEW
                     ORDER SIBLINGS BY T.NURSING_PLAN_ID ASC, T.DISPLAY_ORDER ASC；

opengauss语句：

SELECT ROWNUM, T.*, CASE
                                     WHEN T.PARENT_CODE IS NOT NULL THEN
                                      T.NURSING_PLAN_ID || '_' || T.PARENT_CODE
                                   END AS PARENT_CODE_NEW,
                                   T.NURSING_PLAN_ID || '_' || T.NURSING_PLAN_SUB_ID AS SUB_ID_NEW
                      FROM (                     with RECURSIVE cte as (SELECT C.*
                              FROM JHNIS_NURSING_PLAN_ILLNESS B
                              JOIN JHNIS_NURSING_PLAN_DICT C ON B.PLAN_ILL_ID = C.PLAN_ILL_ID
                                                            AND B.HOSPITAL_NO = C.HOSPITAL_NO
                             WHERE B.HOSPITAL_NO = '40068980X4' and c.PARENT_CODE is null
                                                         union ALL
                                                         SELECT C.*
                              FROM JHNIS_NURSING_PLAN_ILLNESS B
                              JOIN JHNIS_NURSING_PLAN_DICT C ON B.PLAN_ILL_ID = C.PLAN_ILL_ID
                                                            AND B.HOSPITAL_NO = C.HOSPITAL_NO
                                                          inner join cte t1
                                                             on t1.NURSING_PLAN_SUB_ID = c.PARENT_CODE
                                                          where B.HOSPITAL_NO = '40068980X4' and c.PARENT_CODE is not null)
                                   select *
                                     from cte) T
                     ORDER  BY T.NURSING_PLAN_ID ASC, T.DISPLAY_ORDER ASC;

解析：

opengauss主要是把start with Connect By Prior拆成两个部分，查询表一致，但条件不一致，用UNION ALL合并

UNION上半部分：

示例中START WITH T.PARENT_CODE_NEW IS NULL这个条件被UNION ALL上方的 WHERE B.HOSPITAL_NO = '40068980X4' and c.PARENT_CODE is null代替

UNION下半部分：

这部分比较复杂，CONNECT BY PRIOR T.SUB_ID_NEW = T.PARENT_CODE_NEW条件被 inner join cte t1  on t1.NURSING_PLAN_SUB_ID = c.PARENT_CODE代替，有个疑问是cte已经在外部被使用with RECURSIVE cte，这里理解为查询完上半部分后，集合数据已存储在cte中，可供下半部分调用，另外where条件中需增加c.PARENT_CODE is not null，跟上半部分查询结果区分开，实际使用中需注意

最后结尾处增加 select * from cte即可