正體說，玫瑰即使改變了名字，她的芳香仍然不便
WITH T AS (SELECT (TO_DATE('20080101', 'YYYYMMDD') - 1 + LEVEL) AS DAY FROM DUAL CONNECT BY LEVEL <= (DATE'2108-12-31'-DATE'2008-01-01'))
SELECT year,DECODE(MOFY,TO_CHAR(DAY+(1-DOFW),'MM'),DECODE(SIGN((1-DOFW)),-1,NULL,TO_CHAR(DAY+(1-DOFW),'YYYYMMDD')),NULL) "1",DECODE(MOFY,TO_CHAR(DAY+(2-DOFW),'MM'),DECODE(SIGN((2-DOFW)),-1,NULL,TO_CHAR(DAY+(2-DOFW),'YYYYMMDD')),NULL) "2",DECODE(MOFY,TO_CHAR(DAY+(3-DOFW),'MM'),DECODE(SIGN((3-DOFW)),-1,NULL,TO_CHAR(DAY+(3-DOFW),'YYYYMMDD')),NULL) "3",DECODE(MOFY,TO_CHAR(DAY+(4-DOFW),'MM'),DECODE(SIGN((4-DOFW)),-1,NULL,TO_CHAR(DAY+(4-DOFW),'YYYYMMDD')),NULL) "4",DECODE(MOFY,TO_CHAR(DAY+(5-DOFW),'MM'),DECODE(SIGN((5-DOFW)),-1,NULL,TO_CHAR(DAY+(5-DOFW),'YYYYMMDD')),NULL) "5",DECODE(MOFY,TO_CHAR(DAY+(6-DOFW),'MM'),DECODE(SIGN((6-DOFW)),-1,NULL,TO_CHAR(DAY+(6-DOFW),'YYYYMMDD')),NULL) "6",DECODE(MOFY,TO_CHAR(DAY+(7-DOFW),'MM'),DECODE(SIGN((7-DOFW)),-1,NULL,TO_CHAR(DAY+(7-DOFW),'YYYYMMDD')),NULL) "7"
FROM(SELECT year,DAY,WOFM,WOFY,MOFY,DOFW,RANK() OVER(PARTITION BY year,MOFY,WOFY ORDER BY DOFW) AS SD FROM (SELECT to_char(day,'yyyy') year ,DAY,to_char(day,'W') AS WOFM,TO_CHAR(DAY+1,'IW') AS WOFY,TO_CHAR(DAY,'MM') AS MOFY,TO_CHAR(DAY,'D') AS DOFW FROM T))WHERE SD = 1 ORDER BY year,LPAD(MOFY,2,'0'),WOFm,wofy
|
