아래의 테이블을 바탕으로
vafrom | vato | |
---|---|---|
1 | 20180101 | 20180105 |
2 | 20180108 | 20180110 |
3 | 20180501 | 20180516 |
4 | 20180201 | 20180208 |
5 | 20180305 | 20180308 |
6 | 20180310 | 20180312 |
아래와 같이 나오도록 하고 싶습니다.
월별로 기본적으로 5건씩 나오게 하고싶은데 가능할까요? 없는달은 빈값으로..
1 | 20180101 | 20180105 | |
2 | 20180108 | 20180110 | |
3 | 1월 | ||
4 | |||
5 | |||
6 | 20180201 | 20180208 | |
7 | |||
8 | 2월 | ||
9 | |||
10 | |||
11 | 20180305 | 20180308 | |
12 | 20180310 | 20180312 | |
13 | 3월 | ||
14 | |||
15 | |||
16 | |||
17 | |||
18 | 4월은 | ||
19 | 없으므로 | ||
20 | 빈값 | ||
21 | 20180501 | 20180516 | |
22 | |||
23 | 5월 | ||
24 | |||
25 |
자바소스로 구현가능하나 sql로도 가능한지 궁금해서 올려봅니다
1~12월 까지 나오면 더 욱 좋습니다.
4번째열의 월은 표시 안하는 항목입니다 .참고 용
with tmp as ( select '20180101' vafrom, '20180105' vato from dual union all select '20180108' vafrom, '20180110' vato from dual union all select '20180201' vafrom, '20180105' vato from dual union all select '20180305' vafrom, '20180105' vato from dual union all select '20180310' vafrom, '20180105' vato from dual ) SELECT B.NO, A.VAFROM, A.VATO FROM ( SELECT T.* , ROW_NUMBER( ) OVER( PARTITION BY SUBSTR( T.VAFROM, 1, 6 ) ORDER BY T.VAFROM ASC ) P_NO FROM TMP T ) A , ( SELECT TO_CHAR( ADD_MONTHS( TRUNC( YM, 'year' ), CEIL( LEVEL / 5 ) - 1 ), 'YYYYMM' ) YM , ROW_NUMBER( ) OVER( PARTITION BY CEIL( LEVEL / 5 ) - 1 ORDER BY LEVEL ) P_NO , LEVEL NO FROM (SELECT TO_DATE( MIN( VAFROM ), 'YYYYMMDD' ) YM FROM TMP) CONNECT BY LEVEL <= 60 ) B WHERE B.YM = SUBSTR( A.VAFROM(+), 1, 6 ) AND B.P_NO = A.P_NO(+) ORDER BY B.NO ASC
WITH tmp AS ( SELECT '20180101' vafrom, '20180105' vato FROM dual UNION ALL SELECT '20180108', '20180110' FROM dual UNION ALL SELECT '20180501', '20180516' FROM dual UNION ALL SELECT '20180201', '20180208' FROM dual UNION ALL SELECT '20180305', '20180308' FROM dual UNION ALL SELECT '20180310', '20180312' FROM dual ) SELECT a.ym , b.rn , c.vafrom, c.vato FROM (SELECT TO_CHAR(ADD_MONTHS(sym, LEVEL - 1), 'yyyymm') ym FROM (SELECT TO_DATE('201801', 'yyyymm') sym -- 시작월 , TO_DATE('201805', 'yyyymm') eym -- 종료월 FROM dual) CONNECT BY LEVEL <= MONTHS_BETWEEN(eym, sym) + 1 ) a CROSS JOIN (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 5) b LEFT OUTER JOIN (SELECT SUBSTR(vafrom, 1, 6) ym , ROW_NUMBER() OVER(PARTITION BY SUBSTR(vafrom, 1, 6) ORDER BY vafrom, vato) rn , vafrom, vato FROM tmp WHERE vafrom >= '201801' || '01' -- 시작월 AND vafrom <= '201805' || '31' -- 종료월 ) c ON a.ym = c.ym AND b.rn = c.rn ORDER BY a.ym, b.rn ;