oracle 本月日历

最新推荐文章于 2021-10-25 16:10:48 发布

原创最新推荐文章于 2021-10-25 16:10:48 发布 · 825 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #null #date #日历

Oracle 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用SQL进行复杂日期聚合查询的方法，通过案例展示了如何按周获取每一天的最小日期值，适用于需要精细化管理时间序列数据的场景。

select week,
       min((case when weekday='1' then day else null end)) "星期日",
       min((case when weekday='2' then day else null end)) "星期一",
       min((case when weekday='3' then day else null end)) "星期二",
       min((case when weekday='4' then day else null end)) "星期三",
       min((case when weekday='5' then day else null end)) "星期四",
       min((case when weekday='6' then day else null end)) "星期五",
       min((case when weekday='7' then day else null end)) "星期六"
from (select day,
              decode(sign(rn-weekday),-1,week,week+1) week,weekday
         from (SELECT to_date(day,'yyyy-mm-dd') day,
                      to_char(to_date(day,'yyyy-mm-dd'),'d') weekday,
                      to_char(to_date(day,'yyyy-mm-dd'),'w') week,
                      row_number() over(partition by to_char(to_date(day,'yyyy-mm-dd'),'w')order by day) rn
                 from (SELECT to_char(add_months(last_day(sysdate), -1)+1,'yyyymmdd') + level -1 day
                         FROM dual
                       connect by level<= to_char(last_day(sysdate),'yyyymmdd')-to_char(add_months(last_day(sysdate), -1)+1,'yyyymmdd')+1
                       ) ) )
GROUP BY week
ORDER BY week;