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
相关资源：各显卡算力对照表！