一條sql輸出本年日曆

臨時想查日曆，windows自帶的只能按月一月一月翻，一時興起，通過sql生成了一個全年的日曆出來，語句如下：

SQL> select
  2   min(to_char(day, 'yyyy-mm')) month,
  3   min(decode(weekday, 1, day)) "Mon",
  4   min(decode(weekday, 2, day)) "Tue",
  5   min(decode(weekday, 3, day)) "Wed",
  6   min(decode(weekday, 4, day)) "Thu",
  7   min(decode(weekday, 5, day)) "Fri",
  8   min(decode(weekday, 6, day)) "Sat",
  9   min(decode(weekday, 7, day)) "Sun"
 10    from (select day,
 11                 month,
 12                 decode(sign(rn - weekday), 1, week + 1, week) week,
 13                 weekday,
 14                 rn
 15            from (select day,
 16                         to_char(day, 'mm') month,
 17                         to_char(day, 'w') week,
 18                         to_char(day, 'd') weekday,
 19                         row_number() over(partition by to_char(day, 'mm'), to_char(day, 'w') order by day) rn
 20                    from (select trunc(sysdate, 'yyyy') + level - 1 day
 21                            from dual
 22                          connect by rownum <= trunc(sysdate+365,'yyyy')-trunc(sysdate,'yyyy')))) a
 23   group by a.month, a.week
 24   order by a.month, a.week
 25  ;
 
MONTH   Mon         Tue         Wed         Thu         Fri         Sat         Sun
------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2008-01                         2008-01-01  2008-01-02  2008-01-03  2008-01-04  2008-01-05
2008-01 2008-01-06  2008-01-07  2008-01-08  2008-01-09  2008-01-10  2008-01-11  2008-01-12
2008-01 2008-01-13  2008-01-14  2008-01-15  2008-01-16  2008-01-17  2008-01-18  2008-01-19
2008-01 2008-01-20  2008-01-21  2008-01-22  2008-01-23  2008-01-24  2008-01-25  2008-01-26
2008-01 2008-01-27  2008-01-28  2008-01-29  2008-01-30  2008-01-31             
2008-02                                                             2008-02-01  2008-02-02
2008-02 2008-02-03  2008-02-04  2008-02-05  2008-02-06  2008-02-07  2008-02-08  2008-02-09
2008-02 2008-02-10  2008-02-11  2008-02-12  2008-02-13  2008-02-14  2008-02-15  2008-02-16
2008-02 2008-02-17  2008-02-18  2008-02-19  2008-02-20  2008-02-21  2008-02-22  2008-02-23
2008-02 2008-02-24  2008-02-25  2008-02-26  2008-02-27  2008-02-28  2008-02-29 
2008-03                                                                         2008-03-01
2008-03 2008-03-02  2008-03-03  2008-03-04  2008-03-05  2008-03-06  2008-03-07  2008-03-08
2008-03 2008-03-09  2008-03-10  2008-03-11  2008-03-12  2008-03-13  2008-03-14  2008-03-15
2008-03 2008-03-16  2008-03-17  2008-03-18  2008-03-19  2008-03-20  2008-03-21  2008-03-22
2008-03 2008-03-23  2008-03-24  2008-03-25  2008-03-26  2008-03-27  2008-03-28  2008-03-29
2008-03 2008-03-30  2008-03-31                                                 
2008-04                         2008-04-01  2008-04-02  2008-04-03  2008-04-04  2008-04-05
2008-04 2008-04-06  2008-04-07  2008-04-08  2008-04-09  2008-04-10  2008-04-11  2008-04-12
2008-04 2008-04-13  2008-04-14  2008-04-15  2008-04-16  2008-04-17  2008-04-18  2008-04-19
2008-04 2008-04-20  2008-04-21  2008-04-22  2008-04-23  2008-04-24  2008-04-25  2008-04-26
2008-04 2008-04-27  2008-04-28  2008-04-29  2008-04-30                         
2008-05                                                 2008-05-01  2008-05-02  2008-05-03
2008-05 2008-05-04  2008-05-05  2008-05-06  2008-05-07  2008-05-08  2008-05-09  2008-05-10
2008-05 2008-05-11  2008-05-12  2008-05-13  2008-05-14  2008-05-15  2008-05-16  2008-05-17
2008-05 2008-05-18  2008-05-19  2008-05-20  2008-05-21  2008-05-22  2008-05-23  2008-05-24
2008-05 2008-05-25  2008-05-26  2008-05-27  2008-05-28  2008-05-29  2008-05-30  2008-05-31
2008-06 2008-06-01  2008-06-02  2008-06-03  2008-06-04  2008-06-05  2008-06-06  2008-06-07
2008-06 2008-06-08  2008-06-09  2008-06-10  2008-06-11  2008-06-12  2008-06-13  2008-06-14
2008-06 2008-06-15  2008-06-16  2008-06-17  2008-06-18  2008-06-19  2008-06-20  2008-06-21
2008-06 2008-06-22  2008-06-23  2008-06-24  2008-06-25  2008-06-26  2008-06-27  2008-06-28
2008-06 2008-06-29  2008-06-30                                                 
2008-07                         2008-07-01  2008-07-02  2008-07-03  2008-07-04  2008-07-05
2008-07 2008-07-06  2008-07-07  2008-07-08  2008-07-09  2008-07-10  2008-07-11  2008-07-12
2008-07 2008-07-13  2008-07-14  2008-07-15  2008-07-16  2008-07-17  2008-07-18  2008-07-19
2008-07 2008-07-20  2008-07-21  2008-07-22  2008-07-23  2008-07-24  2008-07-25  2008-07-26
2008-07 2008-07-27  2008-07-28  2008-07-29  2008-07-30  2008-07-31             
2008-08                                                             2008-08-01  2008-08-02
2008-08 2008-08-03  2008-08-04  2008-08-05  2008-08-06  2008-08-07  2008-08-08  2008-08-09
2008-08 2008-08-10  2008-08-11  2008-08-12  2008-08-13  2008-08-14  2008-08-15  2008-08-16
2008-08 2008-08-17  2008-08-18  2008-08-19  2008-08-20  2008-08-21  2008-08-22  2008-08-23
2008-08 2008-08-24  2008-08-25  2008-08-26  2008-08-27  2008-08-28  2008-08-29  2008-08-30
2008-08 2008-08-31                                                             
2008-09             2008-09-01  2008-09-02  2008-09-03  2008-09-04  2008-09-05  2008-09-06
2008-09 2008-09-07  2008-09-08  2008-09-09  2008-09-10  2008-09-11  2008-09-12  2008-09-13
2008-09 2008-09-14  2008-09-15  2008-09-16  2008-09-17  2008-09-18  2008-09-19  2008-09-20
2008-09 2008-09-21  2008-09-22  2008-09-23  2008-09-24  2008-09-25  2008-09-26  2008-09-27
2008-09 2008-09-28  2008-09-29  2008-09-30                                     
2008-10                                     2008-10-01  2008-10-02  2008-10-03  2008-10-04
2008-10 2008-10-05  2008-10-06  2008-10-07  2008-10-08  2008-10-09  2008-10-10  2008-10-11
2008-10 2008-10-12  2008-10-13  2008-10-14  2008-10-15  2008-10-16  2008-10-17  2008-10-18
2008-10 2008-10-19  2008-10-20  2008-10-21  2008-10-22  2008-10-23  2008-10-24  2008-10-25
2008-10 2008-10-26  2008-10-27  2008-10-28  2008-10-29  2008-10-30  2008-10-31 
2008-11                                                                         2008-11-01
2008-11 2008-11-02  2008-11-03  2008-11-04  2008-11-05  2008-11-06  2008-11-07  2008-11-08
2008-11 2008-11-09  2008-11-10  2008-11-11  2008-11-12  2008-11-13  2008-11-14  2008-11-15
2008-11 2008-11-16  2008-11-17  2008-11-18  2008-11-19  2008-11-20  2008-11-21  2008-11-22
2008-11 2008-11-23  2008-11-24  2008-11-25  2008-11-26  2008-11-27  2008-11-28  2008-11-29
2008-11 2008-11-30                                                             
2008-12             2008-12-01  2008-12-02  2008-12-03  2008-12-04  2008-12-05  2008-12-06
2008-12 2008-12-07  2008-12-08  2008-12-09  2008-12-10  2008-12-11  2008-12-12  2008-12-13
2008-12 2008-12-14  2008-12-15  2008-12-16  2008-12-17  2008-12-18  2008-12-19  2008-12-20
2008-12 2008-12-21  2008-12-22  2008-12-23  2008-12-24  2008-12-25  2008-12-26  2008-12-27
2008-12 2008-12-28  2008-12-29  2008-12-30  2008-12-31                          
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7607759/viewspace-220372/，如需轉載，請註明出處，否則將追究法律責任。