oracle的周、月份、日历

78 阅读 0 评论 52 点赞

我是靠谱客的博主无奈樱桃，最近开发中收集的这篇文章主要介绍oracle的周、月份、日历，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

http://hi.baidu.com/fishman0923/blog/item/28879dc1a869a5df38db49d2.html

oracle的周、月份、日历

1、周

/* 关键在于当年第一周的第一天和最后一天怎么算，后面只需加上7天就是一周，类推

--还有就是确定的是，一年总共只有53周

--Oracle中周日为第一天，周六为最后一天 */

SELECT LEVEL 周次,

    (Trunc(SYSDATE,'yyyy')-7) + (7-To_Char(Trunc(SYSDATE,'yyyy'),'d')+1) + (LEVEL-1)*7 当周第一天,

    (Trunc(SYSDATE,'yyyy')-7) + (7-To_Char(Trunc(SYSDATE,'yyyy'),'d')+1) + (LEVEL-1)*7+6 当周最后一天

FROM dual

CONNECT BY LEVEL<=53;

2、月份

--当年每个月的起止时间

select aa as monNum, to_date(mon,'yyyymmdd') as firstDay,LAST_DAY(to_date(mon,'yyyymmdd')) as lastDay

   from (

         select to_char(sysdate,'yyyy')||to_char(aa,'09')||'01' mon ,aa

           from (

                  select 1 aa from dual union

                  select 2 aa from dual union

                  select 3 aa from dual union

                  select 4 aa from dual union

                  select 5 aa from dual union

                  select 6 aa from dual union

                  select 7 aa from dual union

                  select 8 aa from dual union

                  select 9 aa from dual union

                  select 10 aa from dual union

                  select 11 aa from dual union

                  select 12 aa from dual

                )

        );

3、日历

--from:http://www.itpub.net/469543.html

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

4、应用

--每周的上报情况，由于跨年情况存在，故在第几周前加年份缀，如 [2010]-7 ；

select weekNumCur,   sum(上报数) from (

select eventID ,上报数,'['||to_char(createtime,'yyyy')||']-'||to_number(to_char(createtime,'ww')) as weekNumCur , createtime

from statuser.tostat

where createtime between to_date('2010-12-01','yyyy-mm-dd') and to_date('2011-01-10','yyyy-mm-dd')+1 )

group by weekNumCur

order by weekNumCur;

--------------------

时间（周）上报数

[2010]-50 1

[2010]-51 16

[2010]-52 2

[2011]-1 6