构建显示全年日历的SQL

要求:

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

分析:

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

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

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

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

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

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

包括全年的天值,从11日到1231日,共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/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值