今天群里网友在聊用SQL写日历,于是自己也写了一下,2种方式
一、
with ltz as
(select trunc(sysdate, 'MM') + level - 1 as d,
to_char(trunc(sysdate, 'MM') + level - 1, 'd') wd,
to_char(trunc(sysdate, 'MM') + level, 'iw') iw
from dual
connect by level <=
trunc(add_months(sysdate, 1), 'MM') - trunc(sysdate, 'MM'))
select max(decode(wd, 1, d, null)) as Sunday,
max(decode(wd, 2, d, null)) as Monday,
max(decode(wd, 3, d, null)) as Tuesday,
max(decode(wd, 4, d, null)) as Sanday,
max(decode(wd, 5, d, null)) as Siday,
max(decode(wd, 6, d, null)) as Friday,
max(decode(wd, 7, d, null)) as Saturday,
iw
from ltz t
group by iw
order by iw
二、
with ltz as
(select trunc(sysdate, 'MM') + level - 1 as d,
to_char(trunc(sysdate, 'MM') + level - 1, 'D') title,
to_char(trunc(sysdate, 'MM') + level, 'iw') iw
from dual
connect by level <= trunc(add_months(sysdate, 1), 'MM') - trunc(sysdate, 'MM'))
select *
from ltz t pivot(max(d)
for title in(1 as Sunday,
2 as Monday,
3 as Tuesday,
4 as Zhousan,
5 as Zhousi,
6 as Friday,
7 as Saturday))
order by iw