Oracle的SQL实现日期的功能

570w4k4h09

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