drop table #data
create table #data (name varchar(50),begindate date,enddate date)
insert into #data values('甲一','2021-01-02','2021-03-31')
insert into #data values('甲二','2020-12-05','2021-02-28')
select * from #data
with cte as(
select dateadd(dd,-datepart(dd,dateadd(mm,1,begindate)),dateadd(mm,1,begindate)) as startmonth,name,begindate,enddate
from #data
union all
select
--dateadd(mm,1,A.startmonth) as 加一月 --用加一月去比对则正常
dateadd(dd,-datepart(dd,dateadd(mm,1,a.startmonth)),dateadd(mm,1,a.startmonth)) as startmonth
--用加一月并算月末最后以自然日比对,则递归次数超限
,a.name,A.begindate,A.enddate from cte A
join #data B on A.name=B.name and A.begindate=B.begindate and a.enddate=b.enddate
where --dateadd(mm,1,A.startmonth)<=B.enddate --用加一月去比对,则正常
dateadd(dd,datepart(dd,dateadd(mm,1,a.startmonth)),dateadd(mm,1,a.startmonth))<=B.enddate
)--用加一月并算月末最后以自然日比对,则递归次数超限
select * from cte order by name,startmonth
递归中,停止的依据是,生成的startmonth达到了enddate的月份(忽略自然日)
为什么只是用月末比对,就超限制了?