需求：select min(start_date),max(end_date),poor_num,jdlk_num,fpdw_type,ident_id from tmp001 group by poor_num,jdlk_num,fpdw_type,ident_id;
tmp001数据4条：
start_date，end_date,poor_num,jdlk_num,fpdw_type,ident_id
insert into tmp001 values(date'2019-01-01',date'2019-12-31',8,2,'产业','123456');
insert into tmp001 values(date'2020-04-01',date'2020-06-30',4,1,'其他','123456');
insert into tmp001 values(date'2020-07-01',date'2020-09-30',3,0,'其他','123456');
insert into tmp001 values(date'2020-10-01',date'2020-12-31',4,1,'其他','123456');需要判断当年范围内，时间点是否连续，连续可直接select min(start_date),max(end_date),poor_num,jdlk_num,fpdw_type,ident_id from tmp001 group by poor_num,jdlk_num,fpdw_type,ident_id;
如上时间点不连续这条语句就会把2020-04-01和2020-10-01这两个变成一条了，但其实它们中间还有个2020-07-01这一条。
所以有问题，这样的SQL应该如何写才能达成需求呢？
with t(start_date,end_date,poor_num,jdlk_num,fpdw_type,ident_id) as (
select '2019-1-1','2019-12-31',8,2,'',''
union all select '2020-4-1','2020-6-30',8,2,'',''
union all select '2020-7-1','2020-9-30',8,2,'',''
union all select '2020-10-1','2020-12-31',8,2,'',''
union all select '2021-1-1','2021-3-30',8,2,'',''
union all select '2021-5-1','2021-7-31',8,2,'',''
),t1 as (
select CONVERT(date,start_date) as sd
,CONVERT(date,end_date) as ed
,YEAR(start_date) as y
from t
),t2 as (
select *,DATEDIFF(DAY,sd,ed)+1 as days,DATEDIFF(DAY,msd,med)+1 as mdays
from t1 a
cross apply (
select MIN(sd) as msd,MAX(ed) as med
from t1
where y=a.y
) b
),t3 as (
select y,msd,med,mdays,SUM(days) as sdays
from t2
group by y,msd,med,mdays
)
select *,(case when mdays<>sdays then '日期不连续，中间丢失' + CONVERT(varchar,mdays-sdays) + '天' else '日期连续' end) as result
from t3
y msd med mdays sdays result
----------- ---------- ---------- ----------- ----------- ----------------------------------------------------
2019 2019-01-01 2019-12-31 365 365 日期连续
2020 2020-04-01 2020-12-31 275 275 日期连续
2021 2021-01-01 2021-07-31 212 181 日期不连续，中间丢失31天
(3 行受影响)对oracle不熟悉，用mssql给你写了一个，你参考一下，如果对你有帮助，还望采纳