1. 년월
2. 날짜
3. 추가한컬럼
WITH test AS
(
SELECT '201206' as yyyyMM ,1 as dd ,100 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,2 as dd ,100 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,3 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,4 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,5 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,6 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,7 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,8 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,8 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,9 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,10 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,11 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,12 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,13 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,14 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,15 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,16 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,17 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,18 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,19 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,20 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,21 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,22 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,23 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,24 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,25 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,26 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,27 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,28 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,29 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,30 as dd ,200 as d2 FROM dual union ALL
SELECT '201206' as yyyyMM ,31 as dd ,200 as d2 FROM dual )
SELECT *
FROM test A
RIGHT OUTER JOIN
(SELECT LEVEL X
FROM DUAL
CONNECT BY LEVEL <= TO_CHAR (LAST_DAY (TO_DATE ('201206', 'yyyyMM')), 'DD')) D
ON A.dd = D.X
기존에 이런식으로 한달기간만 보여주다가 d2라는 컬럼을 키로 추가해야되서..
d2 * 30(한달) 이런식으로 보여줘야되는데.. 생각보다 힘드네여..ㅠㅠ
쿼리좀 부탁드려요....
YYYYMM DD D2 X
201206 1 100 1
201206 2 100 2
201206 3 0 3
201206 4 0 4
201206 5 0 5
201206 6 0 6
:
201206 30 0 30
201206 1 0 1
201206 2 0 2
201206 3 200 3
201206 4 200 4
201206 5 200 5
201206 6 300 6
201206 7 300 7
201206 9 300 9
:
201206 30 300 30
이런식의 결과로..D2가 가 한달치만큼 없는날은 0으로 채우고..
음..가능은한가요?
조언이라두 부탁드려요...
감사합니다
|