此方法只是用于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));只要稍微变换一下，就可以实现很多种需求，有兴趣的朋友可以自己试一下。
