오라클(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 |
|
|
|