오라클(Oracle)로 캘린더 만들기

1. SQL :

SELECT

             SUM(DECODE(a.day_num, '1', a.day_cnt, '')) "sunDay",
             SUM(DECODE(a.day_num, '2', a.day_cnt, '')) "monDay",
             SUM(DECODE(a.day_num, '3', a.day_cnt, '')) "tueDay",
             SUM(DECODE(a.day_num, '4', a.day_cnt, '')) "wedDay",
             SUM(DECODE(a.day_num, '5', a.day_cnt, '')) "thuDay",
             SUM(DECODE(a.day_num, '6', a.day_cnt, '')) "friDay",
             SUM(DECODE(a.day_num, '7', a.day_cnt, '')) "satDay"
  FROM
       (SELECT (rownum + 7) - TO_NUMBER(TO_CHAR(TO_DATE(:dateYyyymm||TO_CHAR(rownum), 'YYYYMMDD'), 'd')) day_cal,
               TO_CHAR(TO_DATE(:dateYyyymm||TO_CHAR(rownum), 'YYYYMMDD'), 'd') day_num,
               rownum day_cnt
          FROM all_objects
         WHERE rownum <= TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(:dateYyyymm||'01', 'YYYYMMDD')),'dd'))
       ) a
GROUP BY a.day_cal
ORDER BY a.day_cal

 

※ dataYyyymm : 조회하려는 년월(예: 201412)

 

2. 결과 : dataYyyymm : 201412

 

sunDay	monDay	tueDay	wedDay	thuDay	friDay	satDay
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31