UNION GROUP BY 쿼리를 CASE WHEN으로 대체할 수 있을까요? 0 2 752

by 찡스쿄 [SQL Query] UNION GROUP BY TO CASE WHEN [2021.12.09 14:30:34]


안녕하세요, 선배님들.

하단의 쿼리를 CASE WHEN을 이용해서 동일한 ROW를 결과로 내려받을 수 있을까요?

 /* 1번 */
        SELECT
            DT504_01,
            F1.DT102_01,
            F1.DT103_01,
            F1.DT103_02,
            F1.DT504_03
        FROM
            DT504AC F1, -- T_PAYMENT_WS
            DT201SL F2 -- T_PAYMENT
        WHERE
            LEFT(F1.DT504_06, 4) = '2021'
            AND (F1.DT104_01 = F2.DT104_01)
            AND (F2.DT201_24 = F1.DT504_01 AND LEFT(F2.DT201_27, 4) = '2021')
            AND F1.DT504_06 = REPLACE(LEFT(F2.DT201_27, 7), '-', '')
            AND F2.DT201_25 = (CASE WHEN F1.DT504_05 = '2' THEN '2' END)
        GROUP BY
            DT504_01,
            F1.DT102_01,
            F1.DT103_01,
            F1.DT103_02,
            F1.DT504_03
            
        /* 2번 */
        UNION
        SELECT
            DT504_01,
            F1.DT102_01,
            F1.DT103_01,
            F1.DT103_02,
            F1.DT504_03
        FROM
            DT504AC F1,
            DT201ISL F2
        WHERE
            LEFT(F1.DT504_06, 4) = '2021'
            AND (F1.DT104_01 = F2.DT104_I01)
            AND (
                F2.DT201_I17 = F1.DT504_01
                AND LEFT(F2.DT201_I20, 4) = '2021'
                AND F1.DT504_06 = REPLACE(LEFT(F2.DT201_I20, 7), '-', '')
                AND F2.DT201_I18 = (CASE WHEN F1.DT504_05 = '2' THEN '2' END)
            )
        GROUP BY
            DT504_01,
            F1.DT102_01,
            F1.DT103_01,
            F1.DT103_02,
            F1.DT504_03
            
        /* 3번 */
        UNION
        SELECT
            DT504_01,
            F1.DT102_01,
            F1.DT103_01,
            F1.DT103_02,
            F1.DT504_03
        FROM
            DT504AC F1,
            DT201SL F2
        WHERE
            LEFT(F1.DT504_06, 4) = '2021'
            AND (F1.DT104_01 = F2.DT104_01)
            AND (
                F2.DT201_20 = F1.DT504_01
                AND LEFT(F2.DT201_23, 4) = '2021'
                AND F1.DT504_06 = REPLACE(LEFT(F2.DT201_23, 7), '-', '')
                AND F2.DT201_21 = (CASE WHEN F1.DT504_05 = '1' THEN '2' END)
            )
        GROUP BY
            DT504_01,
            F1.DT102_01,
            F1.DT103_01,
            F1.DT103_02,
            F1.DT504_03
            
        /* 4번 */    
        UNION
        SELECT
            DT504_01,
            F1.DT102_01,
            F1.DT103_01,
            F1.DT103_02,
            F1.DT504_03
        FROM
            DT504AC F1,
            DT201ISL F2
        WHERE
            LEFT(F1.DT504_06, 4) = '2021'
            AND (F1.DT104_01 = F2.DT104_I01)
            AND (
                F2.DT201_I13 = F1.DT504_01
                AND LEFT(F2.DT201_I16, 4) = '2021'
                AND F1.DT504_06 = REPLACE(LEFT(F2.DT201_I16, 7), '-', '')
                AND F2.DT201_I14 = (CASE WHEN F1.DT504_05 = '1' THEN '2' END)
            )
        GROUP BY
            DT504_01,
            F1.DT102_01,
            F1.DT103_01,
            F1.DT103_02,
            F1.DT504_03

 

1번과 3번의 조인하는 테이블이 같고, 2번과 4번의 조인하는 테이블이 같다고 했을 때

UNION을 사용하지 않고 CASE WHEN으로 UNION한 결과와 동일한 ROW를 조회할 수 있을까요?

선배님들의 피드백과 조언 미리 감사드립니다 :)

by 마농 [2021.12.10 08:55:16]
WITH f2 AS
(
-- 1 / 3 --
SELECT gb dt504_05
     , dt104_01
     , CASE gb WHEN '1' THEN dt201_21 WHEN '2' THEN dt201_25 END dt201_gb
     , CASE gb WHEN '1' THEN dt201_20 WHEN '2' THEN dt201_24 END dt504_01
     , CASE gb WHEN '1' THEN dt201_23 WHEN '2' THEN dt201_27 END dt504_06
  FROM dt201sl
     , (SELECT '1' gb UNION ALL SELECT '2') a
 UNION
-- 2 / 4 --
SELECT gb dt504_05
     , dt104_01
     , CASE gb WHEN '1' THEN dt201_114 WHEN '2' THEN dt201_118 END dt201_gb
     , CASE gb WHEN '1' THEN dt201_113 WHEN '2' THEN dt201_117 END dt504_01
     , CASE gb WHEN '1' THEN dt201_116 WHEN '2' THEN dt201_120 END dt504_06
  FROM dt201isl
     , (SELECT '1' gb UNION ALL SELECT '2') a
)
SELECT DISTINCT
       f1.dt504_01
     , f1.dt102_01
     , f1.dt103_01
     , f1.dt103_02
     , f1.dt504_03
  FROM dt504ac f1
     , f2
 WHERE f1.dt104_01 = f2.dt104_01
   AND f1.dt504_01 = f2.dt504_01
   AND f1.dt504_05 = f2.dt504_05
   AND f1.dt504_06 = REPLACE(LEFT(f2.dt504_06, 7), '-', '')
   AND f2.dt504_06 LIKE '2021%'
   AND f2.dt201_gb = '2'
;

 


by 찡스쿄 [2021.12.10 11:01:52]

와...... 놀랍다는 말 이외엔 표현할 방법이 없네요 마농 선생님......

정말 정말 정말 정말 정말 존경합니다.....

너무 감사드려요... 선생님 :)

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입