oracle 实现日历

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('2012'  || '0101', 'yyyymmdd'), 'd') > '1') AND (TO_CHAR(dayofyear, 'd') < TO_CHAR(TO_DATE('2012' || '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('2012' || '0101', 'yyyymmdd') + LEVEL - 1 AS dayofyear
   FROM DUAL
    CONNECT BY LEVEL <= TO_CHAR(TO_DATE('2012' || '1231', 'yyyymmdd'),'ddd')))
 GROUP BY mon, mon_name, new_yweek

原文：http://busyfan.itpub.net/post/31671/288489

我把日期改了一下

 

转载于:https://www.cnblogs.com/baiyixianzi/archive/2012/08/29/plsql08.html