sql 重合时间区间

最新推荐文章于 2024-06-13 13:04:41 发布

永定河里根本没王八

最新推荐文章于 2024-06-13 13:04:41 发布

阅读量120

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/qq_41080336/article/details/132057113

版权

step1.创建数据

create table aaa (
fund_code varchar2(10),
beg_date varchar2(10),
end_date varchar2(10)
);
insert into aaa values ('123456', '20220123', '20220203');
insert into aaa values ('123456', '20220228', '20220303');
insert into aaa values ('123456', '20220301', '20220311');
insert into aaa values ('123456', '20220328', '20220408');
insert into aaa values ('123456', '20220428', '20220508');
insert into aaa values ('123456', '20220501', '20220520');

step2.结果展示

select distinct a1.*,a2.*
from aaa a1, aaa a2
where a1.END_DATE between a2.beg_date and a2.end_date
and a2.end_date!=a1.END_DATE;

step3.结果展示-分析函数

select FUND_CODE, BEG_DATE,END_DATE, to_char(lead_date,'YYYYMMDD') AS 上一起始日期 from
(
select FUND_CODE,
BEG_DATE,
END_DATE,
to_date(END_DATE, 'YYYYMMDD') as end_dt,
lead(to_date(BEG_DATE, 'YYYYMMDD')) over(partition by fund_code order by BEG_DATE) as lead_date
from aaa
)
where LEAD_DATE-end_dt<0 ；