데이터베이스 질문과 답변

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

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

안녕하세요, 선배님들.

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

?
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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
/* 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]
?
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
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() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입