sql，给一个start时间和end时间，其中有m个断点时间，要踢出这m个月的时间，最后返回m+1条数据把区间返回

Question

我有一个表记录了某人id， ，start_date 和end_date 日期，另一个表记录了对应的id人，date 未出席的当月的月末日期，比如1月31日，如果2个月没出席，就有2条数据，我想等得到这个人出席对的时间段，返回要求是一段区间是一行记录，返回 id，区间1-区间2，有3个月没出席的话，返回4条区间，前提是数据配置都正常的情况下

难点在于每个人都未出席的月份时，都会返回m个月+1的数据，以及需要考虑有的人没缺席，缺席一个月，缺席多个月的情况，不能用存储过程

源数据

         表a,  id, start_date, end_date
         表b, id, monthend_date

表a id start_date end_date
1 2021-01-01 2021-12-15
2 2021-01-01 2021-12-15
3 2021-01-01 2021-12-15

表b id monthend_date
1 2021-03-31
2 2021-03-31
2 2021-05-31
备注 这个表的monthend_date 只会出现月末的最后一天日期，来代表这个月都是未出席的

我想要达到的结果

返回
id qujian1 qujian2
1 2021-01-01 2021-02-28
1 2021-04-01 2021-12-15
2 2021-01-01 2021-02-28
2 2021-04-01 2021-04-30
2 2021-06-01 2021-12-15
3 2021-01-01 2021-12-15

Answer 1

用的是什么数据库？

---

--测试数据
create table test_a_20220218 (id int,start_date datetime,end_date datetime);
insert into test_a_20220218 values (1,'2021-01-01','2021-12-15');
insert into test_a_20220218 values (2,'2021-01-01','2021-12-15');
insert into test_a_20220218 values (3,'2021-01-01','2021-12-15');

create table test_b_20220218(id int,monthend_date datetime);
insert into test_b_20220218 values(1,'2021-03-31');
insert into test_b_20220218 values(2,'2021-03-31');
insert into test_b_20220218 values(2,'2021-05-31');

--查询sql (mysql 8.0 以上,sqlserver去掉RECURSIVE,修改日期计算函数也可以)
with RECURSIVE cte as(
select id,start_date , end_date from test_a_20220218 a
union all
select cte.id,DATE_SUB(cte.start_date,INTERVAL -1 DAY),cte.end_date  from cte where DATE_SUB(cte.start_date,INTERVAL -1 DAY)<=cte.end_date
),
t as (
select cte.id,cte.start_date,
date_sub(cte.start_date, interval (rank() over(partition by id order by cte.start_date)) day) gp
from cte where not exists (select 1 from test_b_20220218 b where b.id=cte.id and month(cte.start_date)=month(b.monthend_date))
)
select id,min(start_date),max(start_date) from t group by id,gp