SQL Server 基于入职日期生成至当前月的年月序列及CTE递归问题
递归CTE生成连续年月序列的问题修正
原代码无法生成完整序列的核心原因
- 递归终止条件逻辑完全失效:原逻辑直接对比偏移后的月份数字与当前系统月份数字、年份数字,未结合完整的年月时间轴判断先后。比如入职是2020年7月、当前是2021年1月,偏移1个月得到2020年8月,月份值8大于当前月份值1,递归条件直接不满足,流程在第一次迭代就终止,仅保留锚点的1条初始记录。
- 存在语法漏写问题:锚点部分的
yearNumber = DATEPART(YEAR, DATEADD(m, i+1, @JoiningDate)语句缺少右括号,SQL引擎解析时本身就可能触发隐式截断或报错。 - 日期计算基准混乱:递归层反复基于初始的
@JoiningDate叠加偏移量,未以上一层生成的月份值为基础累加,长期计算容易出现时序偏差。
修正后的可直接运行代码
DECLARE @JoiningDate Date SET @JoiningDate = '2020-07-04' -- 先规整日期到当月1号,排除具体日值对序列生成的干扰 DECLARE @StartMonth DATE = DATEFROMPARTS(YEAR(@JoiningDate), MONTH(@JoiningDate), 1) DECLARE @EndMonth DATE = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) ;With MonthYears as ( -- 锚点:取入职当月的月份、年份值 SELECT monthNumber = MONTH(@StartMonth), yearNumber = YEAR(@StartMonth), currentCalcMonth = @StartMonth -- 记录当前层生成的月份基准,供下一层递归使用 UNION ALL -- 递归层:在上一层的月份基础上加1个月 SELECT monthNumber = MONTH(DATEADD(MONTH, 1, currentCalcMonth)), yearNumber = YEAR(DATEADD(MONTH, 1, currentCalcMonth)), currentCalcMonth = DATEADD(MONTH, 1, currentCalcMonth) FROM MonthYears -- 终止条件:只要下一个生成的月份不超过当前月的1号,就继续递归 WHERE DATEADD(MONTH, 1, currentCalcMonth) <= @EndMonth ) SELECT monthNumber AS MonthNumber, yearNumber AS YearNumber FROM MonthYears OPTION (MAXRECURSION 1000) -- 避免默认递归次数上限(100次)截断长序列
修正逻辑说明
- 把对比维度从拆分的月、年独立值,替换为规整到当月1号的完整日期值,直接对比时间先后,彻底避免“12月比1月的数字大导致逻辑截断”的问题。
- 去掉手动维护的
i偏移变量,递归时直接在上一层的月份基准上加1个月,减少重复计算的误差。 - 增加递归次数上限配置,默认100次递归最多只能生成100个月(约8年多)的序列,调整上限后可满足十几年跨度的年月生成需求。
- 最终查询时只取需要的月份、年份字段,输出格式和预期示例完全一致。
内容的提问来源于stack exchange,提问作者claudia
相关产品推荐
相关产品推荐

