带你轻松接触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;