Oracle使用SQL語(yǔ)句生成日歷的實(shí)現(xiàn)方法
作者：佚名
Oracle使用SQL語(yǔ)句能否實(shí)現(xiàn)日歷的功能呢？答案是肯定的，下文對(duì)該功能的實(shí)現(xiàn)方法作了詳細(xì)的介紹，供您參考學(xué)習(xí)之用。
Oracle使用SQL語(yǔ)句可以實(shí)現(xiàn)日歷的功能，下面就為您詳細(xì)介紹Oracle使用SQL語(yǔ)句生成日歷的實(shí)現(xiàn)方法，希望對(duì)您能有所幫助。
1 要構(gòu)造某年某月的日歷，必須先知道這個(gè)月的開(kāi)始時(shí)間，結(jié)束時(shí)間及天數(shù)
開(kāi)始日期 例如 2006年11月
- select to_date('20061101','yyyymmdd') as startDayOfMon from dual;
結(jié)束日期
- select last_day(to_date('20061101','yyyymmdd')) as endDayOfMon from dual;
日期區(qū)間天數(shù)
- select last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1 as DayOfMon
- from dual;
2 接下來(lái)就是需要得到開(kāi)始時(shí)間到結(jié)束時(shí)間每一天的結(jié)果集
- select * from (
- select to_date('20061101','yyyymmdd') + level - 1 as everyDay from dual
- connect by level <=
- (last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1));
3 再進(jìn)一步則是將該月中的日期分解成第幾周，星期幾。
- select everyDay,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(select to_date('20061101','yyyymmdd') + level - 1 as everyDay from dual
- connect by level <=
- (last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1));
4 這個(gè)結(jié)果集求出來(lái)后,接下拉就是使用DECODE函數(shù)進(jìn)行行列轉(zhuǎn)換了
- select everyDay,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(select to_date('20061101','yyyymmdd') + level - 1 as everyDay from dual
- connect by level <=
- (last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1));
5 再進(jìn)一步就是統(tǒng)計(jì)匯總了,大家發(fā)現(xiàn)一個(gè)小問(wèn)題沒(méi)有？
就是該月的第幾周這里是按本月開(kāi)始是星期幾為開(kāi)始的日期，很有意思，
這樣我們按該日是該年的第幾周則是以今年開(kāi)始日期是星期幾為開(kāi)始日期
- select to_char(everyday,'w') as week,
- 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(select to_date('20061101','yyyymmdd') + level - 1 as everyDay
- from dual
- connect by level <= (last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1)
- )
- group by to_char(everyday,'w');
6 以上日歷基本成功，但還有一個(gè)問(wèn)題，就是一周的開(kāi)始時(shí)間問(wèn)題
- select to_char(everyday,'ww') as week,
- 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(select to_date('20061101','yyyymmdd') + level - 1 as everyDay
- from dual
- connect by level <= (last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1)
- )
- group by to_char(everyday,'ww');
7 這樣雖然可以解決，但還存在問(wèn)題，大家可以考慮下！也可以考慮下年歷怎么做！
- select ceil((to_char(everyday,'dd')+(to_char(to_date('20061101','yyyymmdd'),'d')-1))/7) as week,
- 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(select to_date('20061101','yyyymmdd') + level - 1 as everyDay
- from dual
- connect by level <= (last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1)
- )
- group by ceil((to_char(everyday,'dd')+(to_char(to_date('20061101','yyyymmdd'),'d')-1))/7);
以上是最終的結(jié)果。
【編輯推薦】
責(zé)任編輯：段燃
來(lái)源：
互聯(lián)網(wǎng)
