Oracle打印日历功能

^_^小麦苗^_^
关注文章
TinyMind专栏频道

Oracle用SQL打印日历

1.1 打印当月日历

SELECTMAX(DECODE(DOW,1, D,NULL)) SUN,

MAX(DECODE(DOW,2, D,NULL)) MON,

MAX(DECODE(DOW,3, D,NULL)) TUE,

MAX(DECODE(DOW,4, D,NULL)) WED,

MAX(DECODE(DOW,5, D,NULL)) THU,

MAX(DECODE(DOW,6, D,NULL)) FRI,

MAX(DECODE(DOW,7, D,NULL)) SAT

FROM(SELECTROWNUM D,

ROWNUM-2+ TO_NUMBER(TO_CHAR(TRUNC(SYSDATE,'MM'),'D')) P,

TO_CHAR(TRUNC(SYSDATE,'MM')-1+ROWNUM,'D') DOW

FROM ALL_OBJECTS

WHEREROWNUM<=

TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(SYSDATE)),'DD')))

GROUPBYTRUNC(P /7)

ORDERBY sun NULLSFIRST;

1.2 打印年历

SELECTCASE

WHEN(NEW_YWEEK =MIN(NEW_YWEEK)

OVER(PARTITIONBY MON ORDERBY NEW_YWEEK))THEN

MON_NAME

ELSE

NULL

ENDASMONTH,

NEW_YWEEK AS YWEEK,

ROW_NUMBER()OVER(PARTITIONBY MON ORDERBY 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')

ENDAS NEW_YWEEK,

TO_CHAR(DAYOFYEAR,'d')AS WDAY,

TO_CHAR(DAYOFYEAR,'dd')AS MDAY

FROM(SELECT TO_DATE(&YEAR||'0101','yyyymmdd')+LEVEL-1AS DAYOFYEAR

FROM DUAL

CONNECTBYLEVEL<=

TO_CHAR(TO_DATE(&YEAR||'1231','yyyymmdd'),

'ddd')))

GROUPBY MON, MON_NAME, NEW_YWEEK;

1.3 打印某月日历

1.3.1 方法1

ALTER session set nls_language='SIMPLIFIED CHINESE';

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('20170301','yyyymmdd')+level-1as everyDay

from dual

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

)

groupby to_char(everyday,'ww')

ORDERBY week;

1.3.2 方法2

select to_char(everyday,'ww')as week,

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

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星期六

from(select to_date('20170301','yyyymmdd')+level-1as everyDay

from dual

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

)

groupby to_char(everyday,'ww')

ORDERBY week;


http://jackywood.itpub.net/post/1369/127565

一条SQL语句生成年历。

  1. selectcase
  2. when(new_yweek=min(new_yweek)over(partitionbymonorderbynew_yweek))then
  3. mon_name
  4. else
  5. null
  6. endasmonth,
  7. new_yweekasyweek,
  8. row_number()over(partitionbymonorderbynew_yweek)asmweek,
  9. sum(decode(wday,'1',mday,null))assun,
  10. sum(decode(wday,'2',mday,null))asmon,
  11. sum(decode(wday,'3',mday,null))astue,
  12. sum(decode(wday,'4',mday,null))aswed,
  13. sum(decode(wday,'5',mday,null))asthu,
  14. sum(decode(wday,'6',mday,null))asfri,
  15. sum(decode(wday,'7',mday,null))assat
  16. from(selectdayofyearaseveryday,
  17. to_char(dayofyear,'mm')asmon,
  18. to_char(dayofyear,'Month')asmon_name,
  19. to_char(dayofyear,'w')asmweek,
  20. to_char(dayofyear,'ww')asyweek,
  21. case
  22. when(to_char(to_date(&year||'0101','yyyymmdd'),'d')>'1')and
  23. (to_char(dayofyear,'d')<
  24. to_char(to_date(&year||'0101','yyyymmdd'),'d'))then
  25. to_char(to_char(dayofyear,'ww')+1,'fm00')
  26. else
  27. to_char(dayofyear,'ww')
  28. endasnew_yweek,
  29. to_char(dayofyear,'d')aswday,
  30. to_char(dayofyear,'dd')asmday
  31. from(selectto_date(&year||'0101','yyyymmdd')+level-1asdayofyear
  32. fromdual
  33. connectbylevel<=to_char(to_date(&year||'1231','yyyymmdd'),'ddd')
  34. )
  35. )
  36. groupbymon,mon_name,new_yweek
  37. /

链接是作者的解读，我感觉年历的实现主要有三个步骤
1.生成一年之中所有的日期
2.在上步的基础上，得到每个日期所在周、月、年的具体信息。
3.行列转置

其中new_yweek这部分看的真是云里雾里，它主要解决Oracle to_char函数IW和WW坑爹的问题。

  1. selectto_char(d,'yyyy-mm-dd'),to_char(d,'d')dayofweek,to_char(d,'WW')WW,to_char(d,'IW')IWfrom(
  2. selectto_date('20131229','yyyymmdd')+level-1asdfromdualconnectbylevel<=10);

TO_CHAR(D, D WW IW
---------- - -- --
2013-12-29 1 52 52
2013-12-30 2 52 01
2013-12-31 3 53 01
2014-01-01 4 01 01
2014-01-02 5 01 01
2014-01-03 6 01 01
2014-01-04 7 01 01
2014-01-05 1 01 01
2014-01-06 2 01 02
2014-01-07 3 01 02

已选择10行。


对比日历，发现2013年12月30,31日的IW，均划分到了2014年的第一周

MONTH YWE MWEEK SUN MON TUE WED THU FRI SAT
------ --- ----- ----- ----- ----- ----- ----- ----- -----
12月 49 1 1 2 3 4 5 6 7
50 2 8 9 10 11 12 13 14
51 3 15 16 17 18 19 20 21
52 4 22 23 24 25 26 27 28
53 5 29 30 31

1）ww的算法为每年1月1日为第一周开始，date+6为每一周结尾

　　例如20050101为第一周的第一天，而第一周的最后一天为20050101+6=20050107

　　公式 每周第一天 ：date + 周 * 7 - 7

　　每周最后一天：date + 周 * 7 - 1

2）iw的算法为星期一至星期日算一周，且每年的第一个星期一为第一周，

　　 例如20050101为星期六，所以用iw的算法是前年的53周，而20050103之后才是第一周的开始。

　　公式 每周第一天 ：next_day(date) + 周 * 7 - 7

　　每周最后一天：next_day(date) + 周 * 7 - 1


按照上述算法，WW的结果相差较远，而IW比较贴近我们对于日期的认识。
但是问题是IW存在边界问题。它会认为12月30日和31日是2014年的第一周，以此统计年历，则会出现问题。所以new_yweek解决的应该是这个问题。

可以使用下面的方法解决IW边界问题。

  1. selectcase
  2. when(yweek=min(yweek)over(partitionbymonorderbyyweek))then
  3. mon_name
  4. else
  5. null
  6. endasmonth,
  7. yweekasyweek,
  8. row_number()over(partitionbymonorderbyyweek)asmweek,
  9. sum(decode(wday,'1',mday,null))assun,
  10. sum(decode(wday,'2',mday,null))asmon,
  11. sum(decode(wday,'3',mday,null))astue,
  12. sum(decode(wday,'4',mday,null))aswed,
  13. sum(decode(wday,'5',mday,null))asthu,
  14. sum(decode(wday,'6',mday,null))asfri,
  15. sum(decode(wday,'7',mday,null))assat
  16. from(selectdayofyearaseveryday,
  17. to_char(dayofyear,'mm')asmon,
  18. to_char(dayofyear,'Month')asmon_name,
  19. to_char(dayofyear,'w')asmweek,
  20. max(decode(to_char(dayofyear,'d'),'1',to_char(dayofyear,'iw')+1,to_char(dayofyear,'iw')))over(orderbydayofyear)asyweek,
  21. to_char(dayofyear,'d')aswday,
  22. to_char(dayofyear,'dd')asmday
  23. from(selectto_date(&year||'0101','yyyymmdd')+level-1asdayofyear
  24. fromdual
  25. connectbylevel<=to_char(to_date(&year||'1231','yyyymmdd'),'ddd')
  26. )
  27. )
  28. groupbymon,mon_name,yweek
  29. /


解决IW边界问题:
max(decode(to_char(dayofyear,'d'),'1',to_char(dayofyear, 'iw')+1,to_char(dayofyear, 'iw'))) over(order by dayofyear) as yweek

decode部分，如果日期是周日，则将iw的值+1，以便日历对齐。
而max分析函数部分，解决类似12月30日，31日划分到下一年的问题。
这个实现存在一些问题。
如果元旦是周五，周六或者周日，例如20110101，它是周六，IW会认为这天是2010年的第五十二周。

  1. SQL>selectto_char(to_date('20110101','yyyymmdd'),'IW')fromdual;

  3. TO
  4. --
  5. 52

对于这个问题，我没有解决的方法，但是一个同事有另外一个方案，用自定义的周序列，使用偏移量。

  1. selectcase
  2. when(yweek=min(yweek)over(partitionbymonorderbyyweek))then
  3. mon_name
  4. else
  5. null
  6. endasmonth,
  7. yweekasyweek,
  8. row_number()over(partitionbymonorderbyyweek)asmweek,
  9. sum(decode(wday,'1',mday,null))assun,
  10. sum(decode(wday,'2',mday,null))asmon,
  11. sum(decode(wday,'3',mday,null))astue,
  12. sum(decode(wday,'4',mday,null))aswed,
  13. sum(decode(wday,'5',mday,null))asthu,
  14. sum(decode(wday,'6',mday,null))asfri,
  15. sum(decode(wday,'7',mday,null))assat
  16. from(selectdayofyearaseveryday,
  17. to_char(dayofyear,'mm')asmon,
  18. to_char(dayofyear,'Month')asmon_name,
  19. to_char(dayofyear,'w')asmweek,
  20. ceil(to_number(dayIndex)/7)asyweek,
  21. to_char(dayofyear,'d')aswday,
  22. to_char(dayofyear,'dd')asmday
  23. from(
  24. select
  25. to_date(&year||'0101','yyyymmdd')+level-1asdayofyear,
  26. to_char(s.firstday,'d')+rownumdayIndex
  27. from
  28. dual,(selectto_date(&year||'0101','yyyymmdd')-1 firstdayfromdual)s
  29. connectbylevel<=to_char(to_date(&year||'1231','yyyymmdd'),'ddd')
  30. )
  31. )
  32. groupbymon,mon_name,yweek
  33. /


  1. case when (to_char(to_date(&year||'0101','yyyymmdd'),'d')>'1')
  2. and
  3. (to_char(dayofyear,'d')<to_char(to_date(&year||'0101','yyyymmdd'),'d'))
  4. then
  5. to_char(to_char(dayofyear,'ww')+1,'fm00')
  6. else
  7. to_char(dayofyear,'ww')
  8. endasnew_yweek

以2013年一月为例，元旦是周二（不是周日，满足了Case When的第一个条件）
6,7日是周日、周一，小于元旦的周二，所以所在周+1，那么行列转置后在日历上就下沉了一层。

1 要构造某年某月的日历，必须先知道这个月的开始时间，结束时间及天数
开始日期 例如 2006年11月
select to_date('20061101','yyyymmdd') as startDayOfMon from dual;

结束日期
select last_day(to_date('20061101','yyyymmdd')) as endDayOfMon from dual;

日期区间天数
select last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1 as DayOfMon
from dual;

2 接下来就是需要得到开始时间到结束时间每一天的结果集
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 再进一步则是将该月中的日期分解成第几周，星期几。

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 这个结果集求出来后,接下拉就是使用DECODE函数进行行列转换了
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 再进一步就是统计汇总了,大家发现一个小问题没有？
就是该月的第几周这里是按本月开始是星期几为开始的日期，很有意思，
这样我们按该日是该年的第几周则是以今年开始日期是星期几为开始日期

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 以上日历基本成功，但还有一个问题，就是一周的开始时间问题

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

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);

以上是最终的结果。





About Me

...............................................................................................................................

● 本文整理自网络

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-04-28 09:00 ~ 2017-04-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。





优质内容筛选与推荐>>
1、彻底弄懂css中单位px和em,rem的区别
2、HttpCache缓存扩展方法
3、C# IComparable接口、IComparer接口和CompareTo(Object x)方法、Compare()方法
4、GlusterFS集群文件系统研究
5、几种常见模式识别算法整理和总结


长按二维码向我转账

受苹果公司新规定影响，微信 iOS 版的赞赏功能被关闭，可通过二维码转账支持公众号。

    阅读
    好看
    已推荐到看一看
    你的朋友可以在“发现”-“看一看”看到你认为好看的文章。
    已取消，“好看”想法已同步删除
    已推荐到看一看 和朋友分享想法
    最多200字，当前共字 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字，当前共字

    发送中

    网络异常，请稍后重试

    微信扫一扫
    关注该公众号

    0 │ 收藏 │ 举报




    联系我们

    欢迎来到TinyMind。

    关于TinyMind的内容或商务合作、网站建议，举报不良信息等均可联系我们。

    TinyMind客服邮箱：support@tinymind.net.cn

    TinyMind.net.cn，专业AI技术社区