50M各位高手，请帮忙看这个SQL怎么写？
规则：给定任意日期生成时段序列（序号，开始日期，截止日期），
假如给定的日期在2024/05/01之前（参考例子2020/5/7)：跨2024/05/01的年度和此之前的年度为整年（见下面的例子），之后的第一个年度的截止日期为当年的12月31日，再之后的日期为自然年（生成3段）
假如给定的日期在2024/05/01之后（参考例子2025/2/10)：第一年为整年，第二年截止日期为当年12月31日，再后面为自然年（生成3段）
variable a varchar2(10);
exec :a:='2020-05-07';
with t1 as
(select add_months(to_date(:a,'yyyy-mm-dd'),(level-1)*12) as st_date,add_months(to_date(:a,'yyyy-mm-dd'),level*12)-1 as ed_date,level as rn from dual connect by
add_months(to_date(:a,'yyyy-mm-dd'),level*12)<=case when :a<=date '2024-05-01' then date '2026-04-30' else date '2027-04-30' end),
t2 as
(select t1.st_date,t1.ed_date,t1.rn,(select count(*) from t1) as ct from t1),
t3 as
(select t2.st_date,decode(rn,ct,to_date(extract(year from t2.st_date)||'-12-31','YYYY-MM-DD'),t2.ed_date) as ed_date from t2),
t4 as
(select add_months((select max(ed_date)+1 from t3),(level-1)*12) as st_date,add_months((select max(ed_date)+1 from t3),level*12)-1 as ed_date from dual connect by
level<=3)
select * from t3 union select * from t4;
ST_DATE ED_DATE
------------------- -------------------
2020-05-07 00:00:00 2021-05-06 00:00:00
2021-05-07 00:00:00 2022-05-06 00:00:00
2022-05-07 00:00:00 2023-05-06 00:00:00
2023-05-07 00:00:00 2024-05-06 00:00:00
2024-05-07 00:00:00 2024-12-31 00:00:00
2025-01-01 00:00:00 2025-12-31 00:00:00
2026-01-01 00:00:00 2026-12-31 00:00:00
2027-01-01 00:00:00 2027-12-31 00:00:00
8 rows selected.
exec :a:='2025-02-10';
ST_DATE ED_DATE
------------------- -------------------
2025-02-10 00:00:00 2026-02-09 00:00:00
2026-02-10 00:00:00 2026-12-31 00:00:00
2027-01-01 00:00:00 2027-12-31 00:00:00
2028-01-01 00:00:00 2028-12-31 00:00:00
2029-01-01 00:00:00 2029-12-31 00:00:00
评论
有用 3
