데이터베이스 질문과 답변
안녕하세요
월별로 열로 고정하려는 code가 있습니다
고정값에 매칭되는 값이 있으면 코드와 amt를 넣어주고 없으면 0으로 처리하고싶은데 잘안되네요...
어떻게 처리하면 될까요? DB는 오라클입니다
code
100
200
300
400
500
ym code amt
201801 300 150000
201801 200 200000
201801 400 40000
201801 500 20000
ym code amt
201802 200 200000
201802 400 40000
201802 100 150000
* 결과값
ym code amt
201801 100 0
201801 200 200000
201801 300 150000
201801 400 40000
201801 500 20000
201802 100 150000
201802 200 200000
201802 300 0
201802 400 40000
201802 500 0
by 마농
[2018.10.11 08:47:18]
WITH code_t AS
(
SELECT 100 code FROM dual
UNION ALL SELECT 200 FROM dual
UNION ALL SELECT 300 FROM dual
UNION ALL SELECT 400 FROM dual
UNION ALL SELECT 500 FROM dual
)
, data_t AS
(
SELECT '201801' ym, 300 code, 150000 amt FROM dual
UNION ALL SELECT '201801', 200, 200000 FROM dual
UNION ALL SELECT '201801', 400, 40000 FROM dual
UNION ALL SELECT '201801', 500, 20000 FROM dual
UNION ALL SELECT '201802', 200, 200000 FROM dual
UNION ALL SELECT '201802', 400, 40000 FROM dual
UNION ALL SELECT '201802', 100, 150000 FROM dual
)
SELECT b.ym
, a.code
, NVL(b.amt, 0) amt
FROM code_t a
LEFT OUTER JOIN data_t b
PARTITION BY (b.ym)
ON a.code = b.code
ORDER BY b.ym, a.code
;
by 레알
[2018.10.11 12:10:19]
파티션을 저렇게 사용할수도 있었네요...
마농님 감사합니다 ^^;
by 우리집아찌
[2018.10.11 08:47:25]
어떤 dbms를 쓰는지 명시해주세요.
) 버튼을 클릭하여 작성 하시면 됩니다.