根据oracle的select,做出一年的日历,代码如下:
select
月份,
周次,
decode(sum(nvl(星期日,0)),0,null,sum(nvl(星期日,0))) 星期日,
decode(sum(nvl(星期一,0)),0,null,sum(nvl(星期一,0))) 星期一,
decode(sum(nvl(星期二,0)),0,null,sum(nvl(星期二,0))) 星期二,
decode(sum(nvl(星期三,0)),0,null,sum(nvl(星期三,0))) 星期三,
decode(sum(nvl(星期四,0)),0,null,sum(nvl(星期四,0))) 星期四,
decode(sum(nvl(星期五,0)),0,null,sum(nvl(星期五,0))) 星期五,
decode(sum(nvl(星期六,0)),0,null,sum(nvl(星期六,0))) 星期六
from(
select
everyday 日期,
to_number(to_char(everyday,'mm')) 月份,
ceil((to_char(everyday,'dd')+(to_char(to_date(to_char(everyday,'yyyymm')||'01','yyyymmdd'),'d')-1))/7) 周次,
decode(to_char(everyday,'dy'),'星期日',to_number(to_char(everyday,'dd')),null) 星期日,
decode(to_char(everyday,'dy'),'星期一',to_number(to_char(everyday,'dd')),null) 星期一,
decode(to_char(everyday,'dy'),'星期二',to_number(to_char(everyday,'dd')),null) 星期二,
decode(to_char(everyday,'dy'),'星期三',to_number(to_char(everyday,'dd')),null) 星期三,
decode(to_char(everyday,'dy'),'星期四',to_number(to_char(everyday,'dd')),null) 星期四,
decode(to_char(everyday,'dy'),'星期五',to_number(to_char(everyday,'dd')),null) 星期五,
decode(to_char(everyday,'dy'),'星期六',to_number(to_char(everyday,'dd')),null) 星期六
from
(
select to_date(&year||'0101','yyyymmdd') + level - 1 as everyday from dual
connect by level <= last_day(to_date(&year||'12','yyyymm') ) - to_date(&year||'0101','yyyymmdd') + 1
)
) group by 月份,周次 order by 月份,周次