在很多论坛上见过要求使用SQL实现年日历例子，今天就自己动手写了，代码实现如下：

点击(此处)折叠或打开

----日历实现
with test as
(select level as id_seq, trunc(sysdate, \'y\') + level - 1 as rq
from dual
connect by level <----得出一年的起止日期
add_months(trunc(sysdate, \'y\'), 12) - trunc(sysdate, \'y\') + 1),
test1 as---穷举出一年的所有日期
(select id_seq, rq, to_char(rq, \'iw\') as iw, to_char(rq, \'mm\') as ---iw为一年当中的第几周
from test)
select max(case to_char(rq, \'dy\')
when \'星期一\' then
to_char(rq, \'dd\')
end) as mon,
max(case to_char(rq, \'dy\')
when \'星期二\' then
to_char(rq, \'dd\')
end) as tus,
max(case to_char(rq, \'dy\')
when \'星期三\' then
to_char(rq, \'dd\')
end) as wen,
max(case to_char(rq, \'dy\')
when \'星期四\' then
to_char(rq, \'dd\')
end) as thur,
max(case to_char(rq, \'dy\')
when \'星期五\' then
to_char(rq, \'dd\')
end) as fri,
max(case to_char(rq, \'dy\')
when \'星期六\' then
to_char(rq, \'dd\')
end) as sta,
max(case to_char(rq, \'dy\')
when \'星期日\' then
to_char(rq, \'dd\')
end) as sun
from test1
group by im, iw
order by im, iw

实现的关键点：
1、获取到一年的起止日期
2、使用穷举法列出一年的所有日期
3、熟悉将日期转化成星期、第几周和获取月份的函数
4、根据月份、第几周分组排序即可

ITPub博客

用SQL实现年日历的方法