Orcale生成日历

--生成指定月的日历
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25086534/viewspace-682429/