데이터베이스 질문과 답변
select * from SS_WEXTRA
현재 이 쿼리 결과는 첫번째 사진이고 제가 만들고 싶은 결과값은 2사진입니다.
만들수 있는 결과 값인지... 만든다면 어떻게 해야 하나요
만약 2사진이라는 테이블이 있을때 1사진으로 결과값으로 바꾸려면 어떻게 해야 하나요?
by 마농
[2021.04.12 10:53:49]
WITH ss_ss_wextra AS
(
SELECT 'S0002' empnum, '202002' epyear, '30202' extnam, 1231234 extpay
UNION ALL SELECT 'S0002', '202002', '그냥' , 22222
UNION ALL SELECT 'S0002', '202002', '기타기타', 12342
UNION ALL SELECT 'S0003', '202002', '30202' , 444512
UNION ALL SELECT 'S0003', '202002', '기타' , 21213
UNION ALL SELECT 'S0004', '202002', 'gg' , 1111
)
SELECT empnum, epyear
, MIN(CASE rn WHEN 1 THEN extnam END) extnam_1
, MIN(CASE rn WHEN 1 THEN extpay END) extpay_1
, MIN(CASE rn WHEN 2 THEN extnam END) extnam_2
, MIN(CASE rn WHEN 2 THEN extpay END) extpay_2
, MIN(CASE rn WHEN 3 THEN extnam END) extnam_3
, MIN(CASE rn WHEN 3 THEN extpay END) extpay_3
FROM (SELECT empnum, epyear, extnam, extpay
, ROW_NUMBER() OVER(PARTITION BY empnum, epyear ORDER BY extnam) rn
FROM ss_ss_wextra
) a
GROUP BY empnum, epyear
;
WITH result_1 AS
(
SELECT 'S0002' empnum, '202002' epyear
, '30202' extnam_1, 1231234 extpay_1
, '그냥' extnam_2, 22222 extpay_2
, '기타기타' extnam_3, 12342 extpay_3
UNION ALL SELECT 'S0003', '202002', '30202', 444512, '기타', 21213, NULL, NULL
UNION ALL SELECT 'S0004', '202002', 'gg' , 1111, NULL , NULL, NULL, NULL
)
SELECT *
FROM (SELECT empnum, epyear
, lv
, CASE lv WHEN 1 THEN extnam_1 WHEN 2 THEN extnam_2 WHEN 3 THEN extnam_3 END extnam
, CASE lv WHEN 1 THEN extpay_1 WHEN 2 THEN extpay_2 WHEN 3 THEN extpay_3 END extpay
FROM result_1 a
CROSS JOIN (SELECT 1 lv UNION ALL SELECT 2 UNION ALL SELECT 3) b
) c
WHERE extnam IS NOT NULL
;
) 버튼을 클릭하여 작성 하시면 됩니다.