这种需求的SQL如何写？

Question

需求：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应该如何写才能达成需求呢？

Answer 1

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给你写了一个，你参考一下，如果对你有帮助，还望采纳