ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Orcale生成日历

Orcale生成日历

原创 Linux操作系统 作者:wjychl 时间:2010-12-22 21:17:47 0 删除 编辑

--生成指定月的日历
select max(a) as "日",
       max(b) as "一",
       max(c) as "二",
       max(d) as "三",
       max(e) as "四",
       max(f) as "五",
       max(g) as "六"
  from (select decode(to_char(a, 'd'), 1, to_number(to_char(a, 'dd')), null) a,
               decode(to_char(a, 'd'), 2, to_number(to_char(a, 'dd')), null) b,
               decode(to_char(a, 'd'), 3, to_number(to_char(a, 'dd')), null) c,
               decode(to_char(a, 'd'), 4, to_number(to_char(a, 'dd')), null) d,
               decode(to_char(a, 'd'), 5, to_number(to_char(a, 'dd')), null) e,
               decode(to_char(a, 'd'), 6, to_number(to_char(a, 'dd')), null) f,
               decode(to_char(a, 'd'), 7, to_number(to_char(a, 'dd')), null) g,
               decode(to_char(a, 'd'),
                      1,
                      decode(sign(to_char(a, 'dd') - 8),
                             -1,
                             decode(to_char(a, 'mm'),
                                    1,
                                    decode(to_char(to_date('20101201',
                                                           'yyyy-mm-dd') - 1,
                                                   'iw'),
                                           to_char(a, 'iw'),
                                           1,
                                           to_char(a, 'iw') + 1),
                                    to_char(a, 'iw') + 1),
                             to_char(a, 'iw') + 1),
                      to_char(a, 'iw')) h
          from (select to_date('20101201', 'yyyy-mm-dd') -
                       to_char(to_date('20101201', 'yyyy-mm-dd'), 'dd') +
                       level a
                  from dual
                connect by level <= (select to_char(last_day(to_date('20101201',
                                                                     'yyyy-mm-dd')),
                                                    'dd')
                                       from dual)))
 group by h
 order by "六";

--生成指定年的日历一
select t.months as "月",
       t.mo     as "一",
       t.tu     as "二",
       t.we     as "三",
       t.th     as "四",
       t.fi     as "五",
       t.sa     as "六",
       t.su     as "日"
  from (select decode(order_, 1, mm, null) months,
               t.mo,
               t.tu,
               t.we,
               t.th,
               t.fi,
               t.sa,
               t.su,
               t.order_
          from (select mm,
                       max(mo) mo,
                       max(tu) tu,
                       max(we) we,
                       max(th) th,
                       max(fi) fi,
                       max(sa) sa,
                       max(su) su,
                       max(w) order_
                  from (select decode(d, 1, dd, null) su,
                               decode(d, 2, dd, null) mo,
                               decode(d, 3, dd, null) tu,
                               decode(d, 4, dd, null) we,
                               decode(d, 5, dd, null) th,
                               decode(d, 6, dd, null) fi,
                               decode(d, 7, dd, null) sa,
                               t.*
                          from (select to_char(dates, 'mm') mm,
                                       to_char(dates, 'iw') iw,
                                       to_char(dates, 'w') w,
                                       to_char(dates, 'dd') dd,
                                       to_char(dates, 'd') d,
                                       dates
                                  from (select trunc(to_date(&year || '0101',
                                                             'yyyy-mm-dd'),
                                                     'yyyy') + (level - 1) dates
                                          from dual
                                        connect by trunc(to_date(&year || '0101',
                                                                 'yyyy-mm-dd'),
                                                         'yyyy') + (level - 1) <=
                                                   last_day(add_months(to_date(&year ||
                                                                               '0101',
                                                                               'yyyy-mm-dd'),
                                                                       11)))) t)
                 group by mm, iw
                 order by mm, max(w)) t) t;

--生成指定年的日历二
select case
         when (y = min(y) over(partition by m order by y)) then
          ye || '-' || m
         else
          null
       end as "年-月",
       sum(decode(w, '1', md, null)) as "日",
       sum(decode(w, '2', md, null)) as "一",
       sum(decode(w, '3', md, null)) as "二",
       sum(decode(w, '4', md, null)) as "三",
       sum(decode(w, '5', md, null)) as "四",
       sum(decode(w, '6', md, null)) as "五",
       sum(decode(w, '7', md, null)) as "六"
  from (select to_char(dy, 'yyyy') as ye,
               to_char(dy, 'mm') as m,
               case
                 when (to_char(to_date(&year || '0101', 'yyyy-mm-dd'), 'd') > '1') and
                      (to_char(dy, 'd') <
                      to_char(to_date(&year || '0101', 'yyyy-mm-dd'), 'd')) then
                  to_char(to_char(dy, 'ww') + 1, 'fm00')
                 else
                  to_char(dy, 'ww')
               end as y,
               to_char(dy, 'd') as w,
               to_char(dy, 'dd') as md
          from (select to_date(&year || '0101', 'yyyy-mm-dd') + level - 1 as dy
                  from dual
                connect by level <=
                           to_char(to_date(&year || '1231', 'yyyy-mm-dd'),
                                   'ddd')))
 group by ye, m, y;

各位大牛有什么好的方法,不妨共享一下,谢谢。

修改了几次,终于好了。

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

上一篇: 重新启动数据库
下一篇: oracle生成树
请登录后发表评论 登录
全部评论

注册时间:2010-12-17

  • 博文量
    21
  • 访问量
    18735