oracle+生成+sql语句 Oracle使用SQL语句生成日历的实现方法

Oracle使用SQL语句可以实现日历的功能，下面就为您详细介绍Oracle使用SQL语句生成日历的实现方法，希望对您能有所帮助。

1 要构造某年某月的日历，必须先知道这个月的开始时间，结束时间及天数

开始日期 例如 11月

selectto_date('1101','yyyymmdd')asstartDayOfMonfromdual;

结束日期

selectlast_day(to_date('1101','yyyymmdd'))asendDayOfMonfromdual;

日期区间天数

selectlast_day(to_date('1101','yyyymmdd'))-to_date('1101','yyyymmdd')+1asDayOfMon

fromdual;

2 接下来就是需要得到开始时间到结束时间每一天的结果集

select*from(

selectto_date('1101','yyyymmdd')+level-1aseveryDayfromdual

connectbylevel<=

(last_day(to_date('1101','yyyymmdd'))-to_date('1101','yyyymmdd')+1));

3 再进一步则是将该月中的日期分解成第几周，星期几。

selecteveryDay,to_char(everyday,'yyyy')as年,

to_char(everyday,'mm')as月,

to_char(everyday,'dd')as日,

to_char(everyday,'dy')as星期几,

lpad(to_char(everyday,'w'),6)as该月的第几周,

lpad(to_char(everyday,'ww'),6)as该年的第几周

from(selectto_date('1101','yyyymmdd')+level-1aseveryDayfromdual

connectbylevel<=

(last_day(to_date('1101','yyyymmdd'))-to_date('1101','yyyymmdd')+1));

4 这个结果集求出来后,接下拉就是使用DECODE函数进行行列转换了

selecteveryDay,to_char(everyday,'yyyy')as年,

to_char(everyday,'mm')as月,

to_char(everyday,'dd')as日,

to_char(everyday,'dy')as星期几,

lpad(to_char(everyday,'w'),6)as该月的第几周,

lpad(to_char(everyday,'ww'),6)as该年的第几周,

lpad(decode(to_char(everyday,'dy'),'星期日',to_char(everyday,'dd')),3)as星期日,

lpad(decode(to_char(everyday,'dy'),'星期一',to_char(everyday,'dd')),3)as星期一,

lpad(decode(to_char(everyday,'dy'),'星期二',to_char(everyday,'dd')),3)as星期二,

lpad(decode(to_char(everyday,'dy'),'星期三',to_char(everyday,'dd')),3)as星期三,

lpad(decode(to_char(everyday,'dy'),'星期四',to_char(everyday,'dd')),3)as星期四,

lpad(decode(to_char(everyday,'dy'),'星期五',to_char(everyday,'dd')),3)as星期五,

lpad(decode(to_char(everyday,'dy'),'星期六',to_char(everyday,'dd')),3)as星期六

from(selectto_date('1101','yyyymmdd')+level-1aseveryDayfromdual

connectbylevel<=

(last_day(to_date('1101','yyyymmdd'))-to_date('1101','yyyymmdd')+1));

5 再进一步就是统计汇总了,大家发现一个小问题没有？

就是该月的第几周这里是按本月开始是星期几为开始的日期，很有意思，

这样我们按该日是该年的第几周则是以今年开始日期是星期几为开始日期

selectto_char(everyday,'w')asweek,

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

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

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

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

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

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

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

from(selectto_date('1101','yyyymmdd')+level-1aseveryDay

fromdual

connectbylevel<=(last_day(to_date('1101','yyyymmdd'))-to_date('1101','yyyymmdd')+1)

)

groupbyto_char(everyday,'w');

6 以上日历基本成功，但还有一个问题，就是一周的开始时间问题

selectto_char(everyday,'ww')asweek,

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

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

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

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

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

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

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

from(selectto_date('1101','yyyymmdd')+level-1aseveryDay

fromdual

connectbylevel<=(last_day(to_date('1101','yyyymmdd'))-to_date('1101','yyyymmdd')+1)

)

groupbyto_char(everyday,'ww');

7 这样虽然可以解决，但还存在问题，大家可以考虑下！也可以考虑下年历怎么做！

selectceil((to_char(everyday,'dd')+(to_char(to_date('1101','yyyymmdd'),'d')-1))/7)asweek,

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

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

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

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

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

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

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

from(selectto_date('1101','yyyymmdd')+level-1aseveryDay

fromdual

connectbylevel<=(last_day(to_date('1101','yyyymmdd'))-to_date('1101','yyyymmdd')+1)

)

groupbyceil((to_char(everyday,'dd')+(to_char(to_date('1101','yyyymmdd'),'d')-1))/7);

以上是最终的结果。

【编辑推荐】

【责任编辑：段燃 TEL：(010)68476606】

点赞 0