oracle列出月历,日历表-专注于Oracle性能调优-51CTO博客

-- 当月的日历表SQL> with x as

2 (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') weekday

6 from dual

7 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) Sun

15 from x

16 group by week

17 order by week

18 /

MO TU WE TH FR SA SU

-- -- -- -- -- -- --

01 02 03 04 05

06 07 08 09 10 11 12

13 14 15 16 17 18 19

20 21 22 23 24 25 26

27 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_year

FROM x0),

x2 AS

/*枚举tdate*/

(SELECT first_year + LEVEL - 1 AS tdate

FROM x1

CONNECT 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')) we

FROM x2),

x4 AS

/*修正周*/

(SELECT tdate,

mon,

CASE

WHEN mon = '12' AND week = '01' THEN

'53'

ELSE

week

END AS week,

we

FROM x3)

SELECT CASE

WHEN lag(mon) over(ORDER BY week) = mon THEN

NULL

ELSE

mon

END 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) sun

FROM x4

GROUP BY mon, week

ORDER 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_mon

4 from dual

5 connect by level <= 4

6 /

QUARTER START_MON END_MON

---------- ---------- ----------

1 2014-01-01 2014-03-31

2 2014-04-01 2014-06-30

3 2014-07-01 2014-09-30

4 2014-10-01 2014-12-31