SQL的魅力是无限的。这里给大家展示一下使用SQL得到日历，同时，用SQL*Plus的break命令格式化显示效果。
sys@ora10g> break on month skip 1
sys@ora10g> col month for a20
sys@ora10g> col Su for a4
sys@ora10g> col Mo for a4
sys@ora10g> col Tu for a4
sys@ora10g> col We for a4
sys@ora10g> col Th for a4
sys@ora10g> col Fr for a4
sys@ora10g> col Sa for a4
sys@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
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
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
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
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
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
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
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
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
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
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
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.
select case
when (new_yweek = min(new_yweek)over(partition by mon order by new_yweek)) then
mon_name
else
null
end as month,
new_yweek as yweek,
row_number() over(partition by mon order by new_yweek) as mweek,
sum(decode(wday, '1', mday, null)) as sun,
sum(decode(wday, '2', mday, null)) as mon,
sum(decode(wday, '3', mday, null)) as tue,
sum(decode(wday, '4', mday, null)) as wed,
sum(decode(wday, '5', mday, null)) as thu,
sum(decode(wday, '6', mday, null)) as fri,
sum(decode(wday, '7', mday, null)) as sat
from (select dayofyear as everyday,
to_char(dayofyear, 'mm') as mon,
to_char(dayofyear, 'Month') as mon_name,
to_char(dayofyear, 'w') as mweek,
to_char(dayofyear, 'ww') as yweek,
case
when (to_char(to_date(&year || '0101', 'yyyymmdd'), 'd') > '1') and
(to_char(dayofyear, 'd') <
to_char(to_date(&year || '0101', 'yyyymmdd'), 'd')) then
to_char(to_char(dayofyear, 'ww') + 1, 'fm00')
else
to_char(dayofyear, 'ww')
end as new_yweek,
to_char(dayofyear, 'd') as wday,
to_char(dayofyear, 'dd') as mday
from (select to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear
from dual
connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')
)
)
group by mon, mon_name, new_yweek
1. 构造全年日期数据
select to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear
from dual
connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')
在这里使用了connect by来构造数据(会受到数据库版本限制,适用于9.2或更高版本),
to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd') 用来计算全年的天数, 这个虽然不是唯一的方法, 但也算是比较简单的方法了
2. 根据全年日期, 得到月/周等数据
select dayofyear as everyday,
...
from (select 全年日期 ...)
里面分别取了 mm/month/w/ww/d/dd 等数据,
其中mm是后面用来排序的, month是后面用来显示月份的(因为month得到的数据不便于月份排序, 而mm的数据显示起来不好),
w和ww在这里是冗余数据, 为了便于看中间数据的,
d 是用来行列转换的, dd是用来显示日期的(即几号)
而那个用case生成的new_yweek是行列转换时的分组标记, 为什么在这里不用IW呢, 而是这么麻烦的用case和ww呢?
因为我最终的显示sunday(星期天)是放在第一列的, 直观表示为一周的第一天, 如果是放在最后一列, 直观表示一周的最后一天的话, 用IW就可以了.
3. 根据周次和月份做行列转换, 把星期日~星期六转为7列
select case ... as month,
...
from (全年各种日期信息数据)
group mon, mon_name, new_yweek
最终的 month 字段显示的是月份名称, 为了省去后面数行相同月份的名称, 因此用了case和分析函数;
yweek是用来表示全年的周次, 从1开始;
mweek是用来表示月中的周次, 每月从1开始;
其后从 sun ~ sat 就是每个星期几对应的月日了
如果之前to_char时用了IW而不是WW的话, 记得要把sun放到最后一列哦, 否则看着就不像日历了, ^_^.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22786751/viewspace-618409/
，如需转载，请注明出处，否则将追究法律责任。
