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

 

10 

11 

12 

13 

14 

15 

16 

17 

18 

19 

20 

 21 

22 

23 

24 

25 

26 

27 

28 

29 

30 

31 

 

 

 

 

 

 

+ Recent posts