-- 当月的日历表
SQL> with x as2 (select trunc(sysdate, 'mm') + level - 1 tdate,3 to_char(trunc(sysdate, 'mm') + level - 1, 'iw') week,4 to_char(trunc(sysdate, 'mm') + level - 1, 'dd') days,5 to_char(trunc(sysdate, 'mm') + level - 1, 'd') weekday6 from dual7 connect by level <= 31)8 select max(case when weekday = 2 then days end) Mon,9 max(case when weekday = 3 then days end) Tus,10 max(case when weekday = 4 then days end) Wed,11 max(case when weekday = 5 then days end) Ths,12 max(case when weekday = 6 then days end) Fri,13 max(case when weekday = 7 then days end) Sat,14 max(case when weekday = 1 then days end) Sun15 from x16 group by week17 order by week18 /MO TU WE TH FR SA SU-- -- -- -- -- -- --01 02 03 04 0506 07 08 09 10 11 1213 14 15 16 17 18 1920 21 22 23 24 25 2627 28 29 30 31
-- 当年的日历表
WITH x0 AS(SELECT 2014 AS years FROM dual),x1 AS(SELECT to_date(years, 'yyyy') AS first_year,add_months(to_date(years, 'yyyy'), 12) AS next_yearFROM x0),x2 AS/*枚举tdate*/(SELECT first_year + LEVEL - 1 AS tdateFROM x1CONNECT BY LEVEL <= next_year - first_year),x3 AS(/*取月份，及周信息*/SELECT tdate,to_char(tdate, 'mm') mon,to_char(tdate, 'iw') week,to_number(to_char(tdate, 'd')) weFROM x2),x4 AS/*修正周*/(SELECT tdate,mon,CASEWHEN mon = '12' AND week = '01' THEN'53'ELSEweekEND AS week,weFROM x3)SELECT CASEWHEN lag(mon) over(ORDER BY week) = mon THENNULLELSEmonEND AS mon,week,MAX(CASE we WHEN 2 THEN tdate END) mon,MAX(CASE we WHEN 3 THEN tdate END) tue,MAX(CASE we WHEN 4 THEN tdate END) wed,MAX(CASE we WHEN 5 THEN tdate END) thr,MAX(CASE we WHEN 6 THEN tdate END) fri,MAX(CASE we WHEN 7 THEN tdate END) sat,MAX(CASE we WHEN 1 THEN tdate END) sunFROM x4GROUP BY mon, weekORDER BY 2
-- 求季度
SQL> select level quarter,2 add_months(trunc(sysdate, 'y'), level * 3 - 3) start_mon,3 add_months(trunc(sysdate, 'y'), level * 3) - 1 end_mon4 from dual5 connect by level <= 46 /QUARTER START_MON END_MON---------- ---------- ----------1 2014-01-01 2014-03-312 2014-04-01 2014-06-303 2014-07-01 2014-09-304 2014-10-01 2014-12-31