利用oracle生成年历

最新推荐文章于 2023-09-12 16:13:00 发布

liuzhao688

最新推荐文章于 2023-09-12 16:13:00 发布

阅读量908

点赞数 2

分类专栏： oracle

本文链接：https://blog.youkuaiyun.com/liuzhao688/article/details/52469899

版权

oracle 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了如何使用Oracle数据库查询语句，通过SQL查询轻松生成年历，包括每个月的天数和日期信息，帮助用户在数据库层面进行日期相关的计算和规划。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

with x as(select
to_char(everyday,'yyyy-mm') as ym,
to_char(everyday,'yyyy') as year,
to_char(everyday,'mm') as month,
case
when to_char(everyday,'mm')='01' and to_char(everyday,'iw')='53' and to_char(everyday,'dy')!='星期日' then 0
when to_char(everyday,'mm')='01' and to_char(everyday,'iw')='53' and to_char(everyday,'dy')='星期日' and to_char(everyday,'dd')='01' then 0
when to_char(everyday,'mm')='01' and to_char(everyday,'iw')='53' and to_char(everyday,'dy')='星期日' then 1
when to_char(everyday,'mm')='01' and to_char(everyday,'iw')='52' then 1
when to_char(everyday,'mmdd')!='0101' and to_char(everyday,'dy')='星期日' then to_number(to_char(everyday,'iw'))+1
when to_char(everyday,'mm')='12' and to_char(everyday,'iw')='01' then 53
else
to_number(to_char(everyday,'iw'))
end as week,
lpad(decode(to_char(everyday,'dy'),'星期日',to_char(everyday,'dd')),3) as 星期日,
lpad(decode(to_char(everyday,'dy'),'星期一',to_char(everyday,'dd')),3) as 星期一,
lpad(decode(to_char(everyday,'dy'),'星期二',to_char(everyday,'dd')),3) as 星期二,
lpad(decode(to_char(everyday,'dy'),'星期三',to_char(everyday,'dd')),3) as 星期三,
lpad(decode(to_char(everyday,'dy'),'星期四',to_char(everyday,'dd')),3) as 星期四,
lpad(decode(to_char(everyday,'dy'),'星期五',to_char(everyday,'dd')),3) as 星期五,
lpad(decode(to_char(everyday,'dy'),'星期六',to_char(everyday,'dd')),3) as 星期六
from(select to_date('20160101','yyyymmdd') + level - 1 as everyDay from dual
connect by level <= last_day(to_date('20201201','yyyymmdd'))-to_date('20160101','yyyymmdd')+1
--(last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1)
))
select case when max(星期日)=' 01' or max(星期一)=' 01' or max(星期二)=' 01' or max(星期三)=' 01' or max(星期四)=' 01' or max(星期五)=' 01' or max(星期六)=' 01' then year||'年'||month||'月' else '' end,max(星期日) SUN,max(星期一) MON,max(星期二) TUE,max(星期三) WED,max(星期四) THU,max(星期五) FRI,max(星期六) SAT from x group by year,month,week,ym order by year,month,week;