Oracle 日历

此方法只是用于Oracle数据库。

直接看例子吧：

select ceil((cdate+(to_char(to_date('20100101','yyyymmdd'),'d'))-2)/7) as week,


sum(decode(to_char(everyday,'d'),'2',to_char(everyday,'dd'))) as 星期一,

sum(decode(to_char(everyday,'d'),'3',to_char(everyday,'dd'))) as 星期二,

sum(decode(to_char(everyday,'d'),'4',to_char(everyday,'dd'))) as 星期三,

sum(decode(to_char(everyday,'d'),'5',to_char(everyday,'dd'))) as 星期四,

sum(decode(to_char(everyday,'d'),'6',to_char(everyday,'dd'))) as 星期五,

sum(decode(to_char(everyday,'d'),'7',to_char(everyday,'dd'))) as 星期六,

sum(decode(to_char(everyday,'d'),'1',to_char(everyday,'dd'))) as 星期日

from(

select everyday,everyday-TO_DATE('20100101','YYYYMMDD')+1 as cdate,decode(to_char(everyday,'d'),1,7,to_char(everyday,'d')-1) as sweek

from(

SELECT NEXT_DAY(TO_DATE('20100101','YYYYMMDD'),2)-7+LEVEL-1 AS everyday FROM DUAL CONNECT BY LEVEL <=(TRUNC(NEXT_DAY(LAST_DAY(TO_DATE('20100101','YYYYMMDD')),2))-(TRUNC(NEXT_DAY(TO_DATE('20100101','YYYYMMDD'),2))-7))

    )

)group by ceil((cdate+(to_char(to_date('20100101','yyyymmdd'),'d'))-2)/7) order by 1

结果---------------------------------------------------------------------------------------------------------------------------

 

如果不需要  本月前后的日期，即  上个月的 28，29，30，31号

只需修改以上代码就可以，当然也可以更简单，如下

select ceil((to_char(everyday,'dd')+(to_char(to_date('20100101','yyyymmdd'),'d'))-2)/7) as week,


sum(decode(to_char(everyday,'d'),'2',to_char(everyday,'dd'))) as 星期一,

sum(decode(to_char(everyday,'d'),'3',to_char(everyday,'dd'))) as 星期二,

sum(decode(to_char(everyday,'d'),'4',to_char(everyday,'dd'))) as 星期三,

sum(decode(to_char(everyday,'d'),'5',to_char(everyday,'dd'))) as 星期四,

sum(decode(to_char(everyday,'d'),'6',to_char(everyday,'dd'))) as 星期五,

sum(decode(to_char(everyday,'d'),'7',to_char(everyday,'dd'))) as 星期六,

sum(decode(to_char(everyday,'d'),'1',to_char(everyday,'dd'))) as 星期日

from(select everyDay,decode(to_char(everyDay,'d'),1,7,to_char(everyDay,'d')-1) as sweek from( select to_date('20100101','yyyymmdd') + level - 1 as everyDay

from dual connect by level <= (last_day(to_date('20100101','yyyymmdd')) - to_date('20100101','yyyymmdd') +1))

)

group by ceil((to_char(everyday,'dd')+(to_char(to_date('20100101','yyyymmdd'),'d'))-2)/7) order by 1;

结果下：-----------------------------------------------------------------------------------------------------------------------

 

PS：年历的实现方式

只需要修改 红色部分代码中的日期既可以实现，我在这里不贴出效果图了，当然oracle也提供了年历的代码，

不过是以周日作为一周的第一天，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 || '0131', 'yyyymmdd'),'ddd')

)

)


group by mon, mon_name, new_yweek

下面是一个生成全年日历的例子

样式如下：

DT         YR   MM DD DAYOFWEEK    WE Q BO ID
---------- ---- -- -- ------------ -- - -- --
2019-01-01 2019 01 01 星期二       01 1 01   
2019-01-02 2019 01 02 星期三       01 1 01   
2019-01-03 2019 01 03 星期四       01 1 01   
2019-01-04 2019 01 04 星期五       01 1 01   
2019-01-05 2019 01 05 星期六       01 1 01   
2019-01-06 2019 01 06 星期日       01 1 01   
2019-01-07 2019 01 07 星期一       01 1 02   
2019-01-08 2019 01 08 星期二       02 1 02   
2019-01-09 2019 01 09 星期三       02 1 02   
2019-01-10 2019 01 10 星期四       02 1 02   
2019-01-11 2019 01 11 星期五       02 1 02   

代码如下：

select to_char(everyDay,'yyyy-mm-dd') as dt,
   to_char(everyday,'yyyy') as yr,
   to_char(everyday,'mm') as mm,
   to_char(everyday,'dd') as dd,
   to_char(everyday,'dy') as dayofweek,
   /*ORACLE自定义的标准周*/
   to_char(everyday,'WW') as weeknum,
   /*该月的第几周*/
   /*lpad(to_char(everyday,'w'),6) as monthOfWeek,*/
   to_char(everyday,'Q') as qr,
   /*ISO的标准周，通常使用这个*/
   to_char(everyday,'IW') as bourse_week,
   null as id
   from(select trunc(sysdate, 'YYYY') + level - 1 as everyDay from dual
  connect by level <= 
  (add_months(trunc(sysdate,'YYYY'),12)-1  -  trunc(sysdate, 'YYYY') +1));
  

只要稍微变换一下，就可以实现很多种需求，有兴趣的朋友可以自己试一下。