解读oracle日历

解读oracle日历

===========================================================

作者: jackywood(http://jackywood.itpub.net)
发表于: 2006.06.28 00:15
分类: 技术探讨
出处: http://jackywood.itpub.net/post/1369/127565
---------------------------------------------------------------

自己在 2005-12-22 itpub上发过一个名为"新的一年快到了, 给大家送个oracle的日历吧"的帖子,
原贴地址: http://www.itpub.net/469543.html ;
今天解读其中生成日历的sql, 希望有助于大家对一些知识的理解和使用.

 

原贴中的sql如下:

---

  select case
           when (new_yweek = min(new_yweek)over(partition by mon order by new_yweek)) then
             mon_name
           else
             null
         end as month,
         new_yweek as yweek,
         row_number() over(partition by mon order by 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')
                 end as new_yweek,
                 to_char(dayofyear, 'd') as wday,
                 to_char(dayofyear, 'dd') as mday
            from (select to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear
                    from dual
                  connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')
                 )
         )
   group by mon, mon_name, new_yweek
  /

---

1. 构造全年日期数据

---

  select to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear
    from dual
   connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')

---

在这里使用了connect by来构造数据(会受到数据库版本限制,适用于9.2或更高版本),
to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd') 用来计算全年的天数, 这个虽然不是唯一的方法, 但也算是比较简单的方法了

2. 根据全年日期, 得到月/周等数据

---

  select dayofyear as everyday,
         ...
    from (select 全年日期 ...)

---

里面分别取了 mm/month/w/ww/d/dd 等数据, 
其中mm是后面用来排序的, month是后面用来显示月份的(因为month得到的数据不便于月份排序, 而mm的数据显示起来不好), 
w和ww在这里是冗余数据, 为了便于看中间数据的,
d 是用来行列转换的, dd是用来显示日期的(即几号)
而那个用case生成的new_yweek是行列转换时的分组标记, 为什么在这里不用IW呢, 而是这么麻烦的用case和ww呢?
因为我最终的显示sunday(星期天)是放在第一列的, 直观表示为一周的第一天, 如果是放在最后一列, 直观表示一周的最后一天的话, 用IW就可以了.

3. 根据周次和月份做行列转换, 把星期日~星期六转为7列

---

  select case ... as month,
         ...
    from (全年各种日期信息数据)
   group mon, mon_name, new_yweek

---

最终的 month 字段显示的是月份名称, 为了省去后面数行相同月份的名称, 因此用了case和分析函数;
yweek是用来表示全年的周次, 从1开始;
mweek是用来表示月中的周次, 每月从1开始;
其后从 sun ~ sat 就是每个星期几对应的月日了
如果之前to_char时用了IW而不是WW的话, 记得要把sun放到最后一列哦, 否则看着就不像日历了, ^_^.