【SQL】日曆
SQL的魅力是無限的。這裡給大家展示一下使用SQL得到日曆，同時，用SQL*Plus的break命令格式化顯示效果。
sec@ora10g> break on month skip page 1
sec@ora10g> col month for a20 justify center
sec@ora10g> col Su for a4
sec@ora10g> col Mo for a4
sec@ora10g> col Tu for a4
sec@ora10g> col We for a4
sec@ora10g> col Th for a4
sec@ora10g> col Fr for a4
sec@ora10g> col Sa for a4
sec@ora10g> sec@ora10g> select LPAD (Month, 20 - (20 - LENGTH (month)) / 2) month,
2 "Su",
3 "Mo",
4 "Tu",
5 "We",
6 "Th",
7 "Fr",
8 "Sa"
9 from (select TO_CHAR (dt, 'fmMonthfm YYYY') month,
10 case
11 when TO_CHAR (dt, 'fmMonthfm YYYY') like 'Dec%'
12 and TO_CHAR (dt + 1, 'iw') = '01'
13 then
14 '53'
15 when TO_CHAR (dt, 'fmMonthfm YYYY') like 'Jan%'
16 and TO_CHAR (dt + 1, 'iw') = '53'
17 then
18 '.5'
19 else
20 TO_CHAR (dt + 1, 'iw')
21 end
22 week,
23 MAX(DECODE (TO_CHAR (dt, 'd'),
24 '1', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
25 "Su",
26 MAX(DECODE (TO_CHAR (dt, 'd'),
27 '2', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
28 "Mo",
29 MAX(DECODE (TO_CHAR (dt, 'd'),
30 '3', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
31 "Tu",
32 MAX(DECODE (TO_CHAR (dt, 'd'),
33 '4', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
34 "We",
35 MAX(DECODE (TO_CHAR (dt, 'd'),
36 '5', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
37 "Th",
38 MAX(DECODE (TO_CHAR (dt, 'd'),
39 '6', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
40 "Fr",
41 MAX(DECODE (TO_CHAR (dt, 'd'),
42 '7', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
43 "Sa"
44 from (select TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
45 from all_objects
46 where ROWNUM <=
47 ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12)
48 - TRUNC (SYSDATE, 'y'))
49 group by TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw')
50 order by TO_CHAR (dt + 1, 'iw'))
51 order by TO_DATE (month, 'Month YYYY'), TO_NUMBER (week)
52 /
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
January 2009 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
February 2009 1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
March 2009 1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
April 2009 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
May 2009 1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
June 2009 1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
July 2009 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
August 2009 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
September 2009 1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
October 2009 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
November 2009 1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
December 2009 1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31
61 rows selected.
盡在SQL & SQL*Plus不言中。
關注一下“break on month skip page 1”和“col month for a20 justify center”SQP*Plus格式化輸出的效果。
-- The End --
sec@ora10g> break on month skip page 1
sec@ora10g> col month for a20 justify center
sec@ora10g> col Su for a4
sec@ora10g> col Mo for a4
sec@ora10g> col Tu for a4
sec@ora10g> col We for a4
sec@ora10g> col Th for a4
sec@ora10g> col Fr for a4
sec@ora10g> col Sa for a4
sec@ora10g> sec@ora10g> select LPAD (Month, 20 - (20 - LENGTH (month)) / 2) month,
2 "Su",
3 "Mo",
4 "Tu",
5 "We",
6 "Th",
7 "Fr",
8 "Sa"
9 from (select TO_CHAR (dt, 'fmMonthfm YYYY') month,
10 case
11 when TO_CHAR (dt, 'fmMonthfm YYYY') like 'Dec%'
12 and TO_CHAR (dt + 1, 'iw') = '01'
13 then
14 '53'
15 when TO_CHAR (dt, 'fmMonthfm YYYY') like 'Jan%'
16 and TO_CHAR (dt + 1, 'iw') = '53'
17 then
18 '.5'
19 else
20 TO_CHAR (dt + 1, 'iw')
21 end
22 week,
23 MAX(DECODE (TO_CHAR (dt, 'd'),
24 '1', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
25 "Su",
26 MAX(DECODE (TO_CHAR (dt, 'd'),
27 '2', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
28 "Mo",
29 MAX(DECODE (TO_CHAR (dt, 'd'),
30 '3', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
31 "Tu",
32 MAX(DECODE (TO_CHAR (dt, 'd'),
33 '4', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
34 "We",
35 MAX(DECODE (TO_CHAR (dt, 'd'),
36 '5', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
37 "Th",
38 MAX(DECODE (TO_CHAR (dt, 'd'),
39 '6', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
40 "Fr",
41 MAX(DECODE (TO_CHAR (dt, 'd'),
42 '7', LPAD (TO_CHAR (dt, 'fmdd'), 2)))
43 "Sa"
44 from (select TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
45 from all_objects
46 where ROWNUM <=
47 ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12)
48 - TRUNC (SYSDATE, 'y'))
49 group by TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw')
50 order by TO_CHAR (dt + 1, 'iw'))
51 order by TO_DATE (month, 'Month YYYY'), TO_NUMBER (week)
52 /
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
January 2009 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
February 2009 1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
March 2009 1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
April 2009 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
May 2009 1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
June 2009 1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
July 2009 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
August 2009 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
September 2009 1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
October 2009 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
November 2009 1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30
MONTH Su Mo Tu We Th Fr Sa
-------------------- ---- ---- ---- ---- ---- ---- ----
December 2009 1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31
61 rows selected.
盡在SQL & SQL*Plus不言中。
關注一下“break on month skip page 1”和“col month for a20 justify center”SQP*Plus格式化輸出的效果。
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-618320/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL查詢日曆SQL
- Oracle用SQL列印日曆OracleSQL
- sql語句建立日曆SQL
- 利用sql server直接建立日曆SQLServer
- 一條sql輸出本年日曆SQL
- Java 周曆日曆Java
- JS編寫日曆控制元件(支援單日曆 雙日曆 甚至多日曆等)JS控制元件
- 日曆2021年日曆表|2021年日曆表列印版 Excel版Excel
- 如何在Mac中使用佛教日曆，波斯日曆等Mac
- 日曆外掛
- 日曆計算
- SQL生成日曆SQL
- vue之實現日曆----顯示農曆，滾動日曆監聽年月改變Vue
- win10 日曆怎麼顯示農曆_win10日曆不顯示農曆怎麼辦Win10
- js手寫日曆JS
- java Calendar日曆類Java
- javascript日曆外掛JavaScript
- 學習日曆-初始
- 整理了SQL Server中是實現日曆的幾個方法SQLServer
- 小程式-日曆簽到
- jquery日曆外掛SimpleCalendarjQuery
- java一個月日曆Java
- 日曆控制元件(Calendar)控制元件
- Google日曆簡易版Go
- Excel動態日曆1Excel
- win10電腦日曆怎麼顯示農曆_win10系統日曆顯示農曆的設定方法Win10
- 前端學習02：jQuery 日曆前端jQuery
- BusyCal for Mac任務日曆工具Mac
- GlanceCal for mac日曆軟體Mac
- 用java實現日曆demo。Java
- BusyCal for Mac(日曆應用程式)Mac
- BusyCal for Mac(任務日曆工具)Mac
- Fantastical 2 for Mac(日曆軟體)ASTMac
- Oracle和JDE日曆轉換Oracle
- 關於日曆程式原始碼原始碼
- Android日曆提醒軟體Android
- 日曆形式實現解析薦
- Google日曆簡易版 2.0Go