ORACLE中创建日历

先来看一下效果

SQL> with x as
  2  (
  3  select * from
  4  (
  5  select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
  6  to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
  7  to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
  8  to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
  9  to_char(sysdate,'mm') mth
 10  from dual
 11  connect by level<=31
 12  )
 13  where curr_mth=mth
 14  )
 15  select
 16  max(case dw when 2 then dm end) Mo,
 17  max(case dw when 3 then dm end) Tu,
 18  max(case dw when 4 then dm end) We,
 19  max(case dw when 5 then dm end) Th,
 20  max(case dw when 6 then dm end) Fr,
 21  max(case dw when 7 then dm end) Sa,
 22  max(case dw when 1 then dm end) Su
 23  from x
 24  group by wk
 25  order by wk;

MO TU WE TH FR SA SU
-- -- -- -- -- -- --
   01 02 03 04 05 06
07 08 09 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31

下面我们把上面这个语句拆开一步步解释一下，首先看一下最里面的那个查询

SQL> select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
  2  to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
  3  to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
  4  to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
  5  to_char(sysdate,'mm') mth
  6  from dual
  7  connect by level<=31;

WK DM         DW CU MT
-- -- ---------- -- --
49 01          3 12 12
49 02          4 12 12
49 03          5 12 12
49 04          6 12 12
49 05          7 12 12
49 06          1 12 12
50 07          2 12 12
50 08          3 12 12
50 09          4 12 12
50 10          5 12 12
50 11          6 12 12

WK DM         DW CU MT
-- -- ---------- -- --
50 12          7 12 12
50 13          1 12 12
51 14          2 12 12
51 15          3 12 12
51 16          4 12 12
51 17          5 12 12
51 18          6 12 12
51 19          7 12 12
51 20          1 12 12
52 21          2 12 12
52 22          3 12 12

WK DM         DW CU MT
-- -- ---------- -- --
52 23          4 12 12
52 24          5 12 12
52 25          6 12 12
52 26          7 12 12
52 27          1 12 12
53 28          2 12 12
53 29          3 12 12
53 30          4 12 12
53 31          5 12 12

31 rows selected.

其中WK代表了这一天在一年中是第几周，DM就是这一天在这个月中是第几天，DW代表了当天日期是星期几（星期1为2，星期2为3……星期7为1），CURR_MTH是结果集中每一天的月份，MTH代表当前月。OK，更进一步看看。

SQL> with x as
  2  (
  3  select * from
  4  (
  5  select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
  6  to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
  7  to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
  8  to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
  9  to_char(sysdate,'mm') mth
 10  from dual
 11  connect by level<=31
 12  )
 13  where curr_mth=mth
 14  )
 15  select
 16  case dw when 2 then dm end as Mo,
 17  case dw when 3 then dm end as Tu,
 18  case dw when 4 then dm end as We,
 19  case dw when 5 then dm end as Th,
 20  case dw when 6 then dm end as Fr,
 21  case dw when 7 then dm end as Sa,
 22  case dw when 1 then dm end as Su
 23  from x;

MO TU WE TH FR SA SU
-- -- -- -- -- -- --
   01
      02
         03
            04
               05
                  06
07
   08
      09
         10
            11

MO TU WE TH FR SA SU
-- -- -- -- -- -- --
               12
                  13
14
   15
      16
         17
            18
               19
                  20
21
   22

MO TU WE TH FR SA SU
-- -- -- -- -- -- --
      23
         24
            25
               26
                  27
28
   29
      30
         31

31 rows selected.

从这个输出结果可以看出，每周的每一天都分别返回一行信息，而且日期所在位置与这天是星期几相对应。现在需要做的就是把每周的所有日期都放入一行中。使用聚合函数MAX，并按照WK来分组，就可以把一周的日期作为一行返回。对结果按WK排序，以保证日历的格式和所有日期的顺序正确。