文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ Oracle语法迁移/ CONNECT BY
更新时间：2024-06-29 GMT+08:00
查看PDF
分享

CONNECT BY

输入：CONNECT BY

1
2
3
4
5
6
7
8
9
 
SELECT id FROM city_branch start with id=roleBranchId connect by prior id=parent_id;
SELECT T.BRANCH_LEVEL, t.ID
                    FROM city_branch c
                   WHERE     (c.branch_level = '1' OR T.BRANCH_LEVEL = '2')
                         AND (T.SIGN = '1' OR T.SIGN = '4' OR T.SIGN = '8')
                         AND T.STATUS = '1'
              START WITH c.ID = I_BRANCH_ID
              CONNECT BY c.ID = PRIOR c.parent_id
                ORDER BY c.branch_level DESC ;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
WITH RECURSIVE migora_cte AS (
     SELECT
               id
               ,1 AS LEVEL
          FROM
               city_branch
          WHERE
               id = roleBranchId
     UNION
     ALL SELECT
             mig_ora_cte_join_alias.id
             ,mig_ora_cte_tab_alias.LEVEL + 1 AS LEVEL
          FROM
             migora_cte mig_ora_cte_tab_alias INNER JOIN city_branch mig_ora_cte_join_alias
                 ON mig_ora_cte_tab_alias.id = mig_ora_cte_join_alias.parent_id
) SELECT
          id
     FROM
          migora_cte
     ORDER BY
          LEVEL
;
 
WITH RECURSIVE migora_cte AS (
     SELECT
               BRANCH_LEVEL
               ,ID
               ,SIGN
               ,STATUS
               ,parent_id
               ,1 AS LEVEL
          FROM
               city_branch c
          WHERE
               c.ID = I_BRANCH_ID
     UNION
     ALL SELECT
               c.BRANCH_LEVEL
               ,c.ID
               ,c.SIGN
               ,c.STATUS
               ,c.parent_id
               ,mig_ora_cte_tab_alias.LEVEL + 1 AS LEVEL
          FROM
               migora_cte mig_ora_cte_tab_alias INNER JOIN city_branch c
                    ON c.ID = mig_ora_cte_tab_alias.parent_id
) SELECT
          BRANCH_LEVEL
          ,ID
     FROM
          migora_cte c
     WHERE
          (
               c.branch_level = '1'
               OR T.BRANCH_LEVEL = '2'
          )
          AND( T.SIGN = '1' OR T.SIGN = '4' OR T.SIGN = '8' )
          AND T.STATUS = '1'
     ORDER BY
          c.branch_level DESC
;

输入：多表CONNECT BY

说明了每个子行与父行的关系。该语法使用CONNECT BY xxx PRIOR子句定义当前行（子行）与前一行（父行）的关系。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
SELECT DISTINCT a.id menuId,
               F.name menuName,
               a.status menuState,
               a.parent_id menuParentId,
               '-1' menuPrivilege,
               a.serialNo menuSerialNo
  FROM CTP_MENU a, CTP_MENU_NLS F
  START WITH a.serialno in (1, 2, 3)
CONNECT BY a.id = PRIOR a.parent_id
       AND f.locale = Language
       AND a.id = f.id
ORDER BY menuId, menuParentId;

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
WITH RECURSIVE migora_cte AS (
        SELECT pr.service_product_id
             , t.enabled_flag
             , pr.operation_id
             , pr.enabled_flag
             , pr.product_code
             , 1 AS LEVEL
          FROM asms.cppsv_operation_sort t
             , asms.cppsv_product_class pr
         WHERE level_id = 3
           AND pr.operation_id = t.operation_id(+)
      UNION ALL 
         SELECT pr.service_product_id
              , t.enabled_flag
              , pr.operation_id
              , pr.enabled_flag
              , pr.product_code
              , mig_ora_cte_tab_alias.LEVEL + 1 AS LEVEL
           FROM migora_cte mig_ora_cte_tab_alias
              , asms.cppsv_operation_sort t
              , asms.cppsv_product_class pr
          WHERE mig_ora_cte_tab_alias.service_product_id = pr.service_product_father_id
            AND pr.operation_id = t.operation_id(+) ) 
SELECT pr.service_product_id
  FROM migora_cte
 WHERE nvl( UPPER( enabled_flag ) ,'Y' ) = 'Y'
   AND nvl( enabled_flag ,'Y' ) = 'Y'
   AND pr.product_code = rec_product1.service_product_code
 ORDER BY LEVEL;
父主题： Oracle语法迁移