Oracle Calendar

声明:

本文为转载,如果有侵犯知识版本，请通知本人，本人将即刻停止侵权行为:

参考网址: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;

结果: