要求:
实现全年按月显示日期的样式
分析:
上面的列:月份 然后按 [星期] 日一二三四五六这样 排列
每行代表着什么,相同的周数。
我们从从一年里的所有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/