文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Oracle语法迁移> CONNECT BY

CONNECT BY

分享
更新时间: 2019/08/09 GMT+08:00

输入:CONNECT BY

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 ; 

输出

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 .. PRIOR子句定义当前行(子行)与前一行(父行)的关系。

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;

输出

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;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区