sql server中具有联合的递归查询

Question

我想将oracle查询转换为sql server。

但在sql server中，“在递归公共表表达式‘TEMP_TAB’的递归部分不允许使用UNION运算符。”出现错误。

我怎么能做到呢？

>> ORACLE查询

   SELECT TREE_ID,
          TREE_NM,
          TREE_LEV_CD,
          UP_TREE_ID,
          TREE_CD,
          TREE_LRK_RUF_ID,
          SCE_XRS_SEQ_VL
     FROM (
           SELECT TREE_ID, 
                  TREE_NM, 
                  TREE_LEV_CD, 
                  UP_TREE_ID,
                  TREE_CD,
                  TREE_LRK_RUF_ID,
                  SCE_XRS_SEQ_VL
             FROM SC_TREE_MASTER
            WHERE TREE_BJ_CD = '02' 
              AND USR_ID = 'ADMIN'
            UNION
           SELECT A.COL_ID TREE_ID,
                  A.COL_NM TREE_NM,
                  B.TREE_LEV_CD TREE_LEV_CD,
                  B.TREE_ID UP_TREE_ID,
                  B.TREE_CD TREE_CD,
                  A.SCM_ID TREE_LRK_RUF_ID,
                  '999' SCE_XRS_SEQ_VL
             FROM SC_COLUMN A,
                  SC_TREE_MASTER B
            WHERE A.SCM_ID = B.TREE_LRK_RUF_ID
              AND B.TREE_BJ_CD = '02'
              AND USR_ID = 'ADMIN'
           ) 
     START WITH UP_TREE_ID = 'TR00000000'
   CONNECT BY PRIOR TREE_ID = UP_TREE_ID
     ORDER SIBLINGS BY TREE_ID, SCE_XRS_SEQ_VL

>> SQL SERVER查询

WITH TEMP_TAB(
             TREE_ID,
             TREE_NM,
             TREE_LEV_CD,
             UP_TREE_ID,
             TREE_CD,
             TREE_LRK_RUF_ID,
             SCE_XRS_SEQ_VL,
             SORT,                         
             RECURSIVE_LEVEL) AS            
     (SELECT A.TREE_ID,
         A.TREE_NM,
         A.TREE_LEV_CD,
         A.UP_TREE_ID,
         A.TREE_CD,
         A.TREE_LRK_RUF_ID,
         A.SCE_XRS_SEQ_VL,
             CAST(CONCAT('-',A.TREE_ID) AS VARCHAR(100)),
             1 RECURSIVE_LEVEL
        FROM (
             SELECT TREE_ID, 
                    TREE_NM, 
                    TREE_LEV_CD, 
                    UP_TREE_ID,
                    TREE_CD,
                    TREE_LRK_RUF_ID,
                    SCE_XRS_SEQ_VL
               FROM SC_TREE_MASTER
              WHERE TREE_BJ_CD = '02' 
                AND USR_ID = 'ADMIN'
              UNION
             SELECT A.COL_ID TREE_ID,
                   A.COL_NM TREE_NM,
                   B.TREE_LEV_CD TREE_LEV_CD,
                   B.TREE_ID UP_TREE_ID,
                   B.TREE_CD TREE_CD,
                   A.SCM_ID TREE_LRK_RUF_ID,
                   '999' SCE_XRS_SEQ_VL
               FROM SC_COLUMN A,
                    SC_TREE_MASTER B
              WHERE A.SCM_ID = B.TREE_LRK_RUF_ID
                AND B.TREE_BJ_CD = '02'
                AND USR_ID = 'ADMIN'
                    ) A
       WHERE A.UP_TREE_ID = 'TR00000000'
      UNION ALL
      SELECT A.TREE_ID,
             A.TREE_NM,
             A.TREE_LEV_CD,
             A.UP_TREE_ID,
             A.TREE_CD,
             A.TREE_LRK_RUF_ID,
             A.SCE_XRS_SEQ_VL,
             CAST(CONCAT(B.SORT,'/',A.TREE_ID) AS VARCHAR(100)),
             RECURSIVE_LEVEL + 1
        FROM (
             SELECT TREE_ID, 
                    TREE_NM, 
                    TREE_LEV_CD, 
                    UP_TREE_ID,
                    TREE_CD,
                    TREE_LRK_RUF_ID,
                    SCE_XRS_SEQ_VL
               FROM SC_TREE_MASTER
              WHERE TREE_BJ_CD = '02' 
                AND USR_ID = 'ADMIN'
              UNION
             SELECT A.COL_ID TREE_ID,
                   A.COL_NM TREE_NM,
                   B.TREE_LEV_CD TREE_LEV_CD,
                   B.TREE_ID UP_TREE_ID,
                   B.TREE_CD TREE_CD,
                   A.SCM_ID TREE_LRK_RUF_ID,
                   '999' SCE_XRS_SEQ_VL
               FROM SC_COLUMN A,
                    SC_TREE_MASTER B
              WHERE A.SCM_ID = B.TREE_LRK_RUF_ID
                AND B.TREE_BJ_CD = '02'
                AND USR_ID = 'ADMIN'
                        ) A,
             TEMP_TAB B
       WHERE B.TREE_ID =A.UP_TREE_ID
     )
    SELECT  TREE_ID, 
        TREE_NM, 
        TREE_LEV_CD, 
        UP_TREE_ID,
        TREE_CD,
        TREE_LRK_RUF_ID,
        SCE_XRS_SEQ_VL
    FROM TEMP_TAB M 
    ORDER BY SORT

Answer 1

T中的递归CTE要求使用'UNION ALL‘。如果需要“UNION”的行为，则在外部查询中使用关键字'DISTINCT‘。