一句sql实现本月日历 & 本年日历

来源：互联网发布：ipad软件助手编辑：程序博客网时间：2024/04/29 14:34

1、一句sql实现本月日历

    select max(su) su,max(mo) mo,max(tu) tu,max(we) we,max(th) th,max(fr) fr,max(sa) sa from
(select case when d=1 then dd end su,
       case when d=2 then dd end mo,
       case when d=3 then dd end tu,
       case when d=4 then dd end we,
       case when d=5 then dd end th,
       case when d=6 then dd end fr,
       case when d=7 then dd end sa,
       d,/*w,*/iw
from(
select dates,to_char(dates,'dd') dd,to_number(to_char(dates,'d')) d,to_char(dates,'w') w,to_char(dates+1,'iw') iw from
(select trunc(sysdate,'month')+level-1 dates from dual
connect by rownum<last_day(sysdate)-trunc(sysdate,'month')+1
)
)
)
group by iw
order by iw;

---注：to_char(date,'iw') 每周固定的从周一开始作为本周的第1天，到周日作为本周的第7天

--------故to_char(date+1,'iw') 才能变成周日是第一天的那种国际日历的周的计算标准

su mo tu we th fr sa （sysdate =2010-7-6）

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

2、一句sql 实现本年日历

SELECT MIN(to_char(DAY, 'yyyymm')) MONTH, MIN(decode(weekday, 1, to_char(DAY, 'dd'))) "星期日",
       MIN(decode(weekday, 2, to_char(DAY, 'dd'))) "星期一",
       MIN(decode(weekday, 3, to_char(DAY, 'dd'))) "星期二",
       MIN(decode(weekday, 4, to_char(DAY, 'dd'))) "星期三",
       MIN(decode(weekday, 5, to_char(DAY, 'dd'))) "星期四",
       MIN(decode(weekday, 6, to_char(DAY, 'dd'))) "星期五",
       MIN(decode(weekday, 7, to_char(DAY, 'dd'))) "星期六"

FROM (SELECT DAY, to_char(DAY, 'mm') MONTH, to_char(DAY, 'd') weekday,
                 (CASE
                  WHEN (to_char(DAY + 1, 'iw') - to_char(DAY, 'mm')) > (53 - 12) THEN
                   -1*to_number(to_char(DAY + 1, 'iw'))
                  ELSE
                   to_number(to_char(DAY + 1, 'iw'))
                END) week    --如果是去年的日期，取周数为负值
           FROM (SELECT trunc(SYSDATE, 'yyyy') + LEVEL - 1 DAY
                    FROM dual
                  CONNECT BY rownum < trunc(SYSDATE + 400, 'yyyy') - trunc(SYSDATE, 'yyyy'))) a
GROUP BY a.month, a.week
ORDER BY a.month, a.week;

to_char(date,'d') 是星期几；

next_day(date,num) date后的下一个周几是多少日期