데이터베이스 질문과 답변
UNION GROUP BY 쿼리를 CASE WHEN으로 대체할 수 있을까요?
0
2
1,088
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]
와...... 놀랍다는 말 이외엔 표현할 방법이 없네요 마농 선생님......
정말 정말 정말 정말 정말 존경합니다.....
너무 감사드려요... 선생님 :)
) 버튼을 클릭하여 작성 하시면 됩니다.