WITH와 PIVOT, CONNECT BY 등을 이용한 메뉴별 권한 조회하는 쿼리 예제

WITH와 PIVOT, CONNECT BY, GROUP 등을 이용한 메뉴별 권한 조회하는 쿼리 예제

(엑셀로 손으로 작업하는 것보다 실수가 없이 정확하다.)

 

SELECT 
            '메뉴ID' AS MENU_ID
          , 'NO' AS NO
          , '1 Depth' AS DEPTH_1
          , '2 Depth' AS DEPTH_2
          , '3 Depth' AS DEPTH_3
          , '4 Depth' AS DEPTH_4
          , '5 Depth' AS DEPTH_5
          , 'Url' AS MENU_URL
          , (SELECT USER_GROUP_NM FROM TB_USER_GROUP WHERE USER_GROUP_ID = '001') AS GROUP_001
          , (SELECT USER_GROUP_NM FROM TB_USER_GROUP WHERE USER_GROUP_ID = '002') AS GROUP_002
          , (SELECT USER_GROUP_NM FROM TB_USER_GROUP WHERE USER_GROUP_ID = '003') AS GROUP_003
          , (SELECT USER_GROUP_NM FROM TB_USER_GROUP WHERE USER_GROUP_ID = '004') AS GROUP_004
FROM DUAL
UNION ALL
SELECT
            A.MENU_ID
          , ROWNUM||'' AS NO
          , CASE WHEN A.MENU_LEVEL = '1' THEN A.MENU_NAME ELSE NULL END AS DEPTH_1
          , CASE WHEN A.MENU_LEVEL = '2' THEN A.MENU_NAME ELSE NULL END AS DEPTH_2
          , CASE WHEN A.MENU_LEVEL = '3' THEN A.MENU_NAME ELSE NULL END AS DEPTH_3
          , CASE WHEN A.MENU_LEVEL = '4' THEN A.MENU_NAME ELSE NULL END AS DEPTH_4
          , CASE WHEN A.MENU_LEVEL = '5' THEN A.MENU_NAME ELSE NULL END AS DEPTH_5
          , A.MENU_URL
          , GROUP_001
          , GROUP_002
          , GROUP_003
          , GROUP_004
FROM TB_MENU_INFO A
        , (
            WITH TUG AS (
                SELECT USER_GROUP_ID
                  FROM TB_USR_GRP
                WHERE USE_AT = 'Y'
            )
            SELECT
                        MENU_ID
                      , MAX(CASE WHEN GROUP_001 = USER_GROUP_ID THEN 'O' ELSE NULL END)  AS GROUP_001
                      , MAX(CASE WHEN GROUP_002 = USER_GROUP_ID THEN 'O' ELSE NULL END)  AS GROUP_002
                      , MAX(CASE WHEN GROUP_003 = USER_GROUP_ID THEN 'O' ELSE NULL END)  AS GROUP_003
                      , MAX(CASE WHEN GROUP_004 = USER_GROUP_ID THEN 'O' ELSE NULL END)  AS GROUP_004
              FROM TUG
              PIVOT(MAX(USER_GROUP_ID) FOR USER_GROUP_ID IN ('001' AS GROUP_001, '002' AS GROUP_002, '003' AS GROUP_003, '004' AS GROUP_004) )
                    , TB_GRP_MENU TGM
            WHERE TGM.USE_AT = 'Y'
            GROUP BY MENU_ID
          ) B
WHERE A.USE_YN = 'Y'
AND A.DEL_YN = 'N'
AND A.MENU_ID = B.MENU_ID
START WITH A.MENU_UPPER_ID IS NULL
CONNECT BY PRIOR A.MENU_ID = A.MENU_UPPER_ID