Oracle 日历

select case
           when (new_yweek = min(new_yweek)over(partition by mon order by new_yweek)) then
             mon_name
           else
             null
         end as month,
         new_yweek as yweek,
         row_number() over(partition by mon order by new_yweek) as mweek,
         sum(decode(wday, '1', mday, null)) as sun,
         sum(decode(wday, '2', mday, null)) as mon,
         sum(decode(wday, '3', mday, null)) as tue,
         sum(decode(wday, '4', mday, null)) as wed,
         sum(decode(wday, '5', mday, null)) as thu,
         sum(decode(wday, '6', mday, null)) as fri,
         sum(decode(wday, '7', mday, null)) as sat
    from (select dayofyear as everyday,
                 to_char(dayofyear, 'mm') as mon,
                 to_char(dayofyear, 'Month') as mon_name,
                 to_char(dayofyear, 'w') as mweek,
                 to_char(dayofyear, 'ww') as yweek,
                 case
                   when (to_char(to_date(&year || '0101', 'yyyymmdd'), 'd') > '1') and
                        (to_char(dayofyear, 'd') <
                        to_char(to_date(&year || '0101', 'yyyymmdd'), 'd')) then
                    to_char(to_char(dayofyear, 'ww') + 1, 'fm00')
                   else
                    to_char(dayofyear, 'ww')
                 end as new_yweek,
                 to_char(dayofyear, 'd') as wday,
                 to_char(dayofyear, 'dd') as mday
            from (select to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear
                    from dual
                  connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')
                 )
         )
   group by mon, mon_name, new_yweek;