构建显示全年日历的ＳＱＬ

要求：

实现全年按月显示日期的样式

分析：

上面的列：月份　然后按　[星期]　日一二三四五六这样　排列

每行代表着什么，相同的周数。

我们从从一年里的所有date，选出符合月份、周序、星期和日期，这样才会一一对应。从date里我们可以算出一天的其它属性，比如月份、一年内的第几周、在月里的日期、在一周里的序号（周序）等，也就可以衍生出另一张表来。这张表包含字段有：日期、月份、一年内的第几周、在月里的日期、在一周里的序号。

有了这张表，我们就可以通过上面学到的行列转换，实现上述目标了。

我们可能通过以下步骤来实现：

1. 创建只有一列的表year_d ，字段为date1 date.

包括全年的天值，从1月1日到12月31日，共365行。

2.创建一张衍生表 year_a 字段为

Date1 date, month number,weeks number,dates number,weekd number

分别对就日期，月份，周序，日序（在一月里的），周几

3.根据上面这张表行列转换，以月份和周序分组和排序，以周几为点，对日序进行转换。

 

步骤：

/**** 创建一个表

1.       求出全年有多少天

Select add_months(trunc(sysdate,'year'),12)-trunc(sysdate,'year') from dual;　365天

2.       创建有一年的所有日期的表

Create table year_d as

select trunc(sysdate,'y')-1 +rownum date1

              from all_objects

             where rownum <=365　365就是上面求出的数字，一年里的天数

说明：从all_objects表里，取出rownum，类似于取1-365这些数字，减1后，从1加起，就实现了得到全年的所有的日期了。

 

3.       创建一张包含日期、月份、一年内的第几周、在月里的日期、在一周里的序号

格式：月mm，周 iw，日期序号 dd， 周号d

create table year_a as

select date1,

to_number(to_char(date1,'mm')) months,

to_number(to_char( date1+1, 'iw' )) weeks,

to_number(to_char(date1,'dd')) dates ,

to_number(to_char(date1,'d')) weekd

from year_d;

 

select * from year_a;

 

4.       实现转换：

select 　　　　months,

     　　　　weeks,

              max(decode(weekd,'1',dates))"日",

              max(decode(weekd,'2',dates)) "一",

              max(decode(weekd,'3',dates)) "二",

              max(decode(weekd,'4',dates)) "三",

              max(decode(weekd,'5',dates)) "四",

              max(decode(weekd,'6',dates)) "五",

         　  max(decode(weekd,'7',dates)) "六"

     from year_a group by months,weeks

order by months,weeks;

/****

select * from

(select months,weeks,weekd,dates from year_a)

  pivot

(sum(dates) for weekd in (1 as "日" ,2 as "一" ,3 as "二",4 as "三",5 as "四",6 as "五",7 as "六"))

Order by months,weeks

/

****/

以上所有的语句，实际上只用到了从sysdate里取本年的第一天，借用了all_objects表取得rownum的序列。当然可以用其他的表，只要行数多于366就行了。

有了以上的基础，咱们就可以把上面的那些语句整合起来，而不需要中间表了。再格式化一下，就可以相当美观了。

 

语句成品：

break on month skip 1

set linesize 120

column month format a20

select lpad( Month, 20-(20-length(month))/2 ) month,

          "日", "一", "二", "三", "四", "五", "六"

      from (

    select to_char(dt,'fmMonthfm') month,

          to_char(dt+1,'iw') week,

           max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "日",

           max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "一",

           max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "二",

           max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "三",

          max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "四",

          max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "五",

          max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "六"

     from ( select trunc(sysdate,'y')-1+rownum dt

              from all_objects

             where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')

   )

   group by to_char(dt,'fmMonthfm'), to_char( dt+1, 'iw' )

          )

    order by to_date( month, 'Month' ), to_number(week)

   /

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10804699/viewspace-712709/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10804699/viewspace-712709/