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
我把日期改了一下
