오라클에서 SQL로 달력 만들기

 

 

select   min(decode(dd, 1, lvl)) as 일,
         min(decode(dd, 2, lvl)) as 월,
         min(decode(dd, 3, lvl)) as 화,
         min(decode(dd, 4, lvl)) as 수,
         min(decode(dd, 5, lvl)) as 목,
         min(decode(dd, 6, lvl)) as 금,
         min(decode(dd, 7, lvl)) as 토
from     (select     to_number(to_char(yyyy + level - 1, 'D')) as dd,
                     to_number(to_char(yyyy + level - 1, 'w')) as ww,
                     to_char(yyyy, 'D') as dd1,
                     level as lvl
          from       (select trunc(sysdate,'mm') as yyyy -- 원하는 달을 가져오기
                      from   dual)   -- trunc(sysdate, 'mm')
          connect by level <= extract(day from last_day(sysdate))) -- 데이터 만들기
group by case when dd >= 1 and dd < dd1 then ww + 1 else ww end
order by case when dd >= 1 and dd < dd1 then ww + 1 else ww end

 

다른 테이블을 이용 하지 않고 달력 만들기