一条Sql语句生成日历

最新推荐文章于 2020-11-26 10:17:38 发布

最新推荐文章于 2020-11-26 10:17:38 发布 · 285 阅读

文章标签：

#SQL

Oracle 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一种使用Oracle SQL进行日期处理的方法，通过复杂的查询构造来获取每个月的第一天及其对应周内的详细日期信息。该方法利用了Oracle特有的函数如TO_CHAR、DECODE等，并结合ROW_NUMBER()等窗口函数实现。

部署运行你感兴趣的模型镜像

select
     min(to_char(day, 'yyyymm')) month,
     min(decode(weekday, 1, day)) "星期日"
     min(decode(weekday, 2, day)) "星期一",
     min(decode(weekday, 3, day)) "星期二",
     min(decode(weekday, 4, day)) "星期三",
     min(decode(weekday, 5, day)) "星期四",
     min(decode(weekday, 6, day)) "星期五",
     min(decode(weekday, 7, day)) "星期六",

     from (select day,
                  month,
                  decode(sign(rn - weekday), 1, week + 1, week) week,
                  weekday,
                  rn
             from (select day,
                          to_char(day, 'mm') month,
                          to_char(day, 'w') week,
                          to_char(day, 'd') weekday,
                          row_number() over(partition by to_char(day, 'mm'), to_char(day, 'w') order by day) rn
                     from (select trunc(sysdate, 'yyyy') + level - 1 day
                             from dual
                           connect by rownum <= trunc(sysdate+365,'yyyy')-trunc(sysdate,'yyyy')))) a
    group by a.month, a.week
    order by a.month, a.week;