声明:
本文为转载,如果有侵犯知识版本,请通知本人,本人将即刻停止侵权行为:
参考网址:http://code.geekinterview.com/oracle/oracle-calendar-sql.html
1 SELECT Initcap(TRIM(To_Char(Dat, 'month'))) || ', ' || 2 To_Char(SYSDATE, 'yyyy') MONTH, 3 MAX(Decode(To_Char(Dat, 'd'), 2, To_Char(Dat, 'dd'))) Mon, 4 MAX(Decode(To_Char(Dat, 'd'), 3, To_Char(Dat, 'dd'))) Tue, 5 MAX(Decode(To_Char(Dat, 'd'), 4, To_Char(Dat, 'dd'))) Wed, 6 MAX(Decode(To_Char(Dat, 'd'), 5, To_Char(Dat, 'dd'))) Thu, 7 MAX(Decode(To_Char(Dat, 'd'), 6, To_Char(Dat, 'dd'))) Fri, 8 MAX(Decode(To_Char(Dat, 'd'), 7, To_Char(Dat, 'dd'))) Sat, 9 MAX(Decode(To_Char(Dat, 'd'), 1, To_Char(Dat, 'dd'))) Sun 10 FROM (SELECT Trunc(SYSDATE, 'y') + Rownum - 1 Dat, 11 To_Char(Trunc(SYSDATE, 'y') + Rownum - 1, 'iw') Woy 12 FROM All_Objects --可以换成ALL_OBJECT 13 WHERE Rownum <= 14 (Add_Months(Trunc(SYSDATE, 'y'), 12) - Trunc(SYSDATE, 'y'))) 15 GROUP BY To_Char(Dat, 'month'), Woy, To_Char(Dat, 'mm') 16 ORDER BY To_Char(Dat, 'mm'), 8;
结果: