如何在SSMS中基于日期列生成多行递增日期的新列？

需求说明 #

原表结构及数据：

需要生成包含NewCol的新表，NewCol是Date列每月递增的日期，且所有生成的日期必须早于等于@EndDate = '2022-05-31'，最终结果如下：

实现方案 #

用T-SQL的递归CTE（公共表表达式）就能实现，具体代码如下：

DECLARE @EndDate DATE = '2022-05-31';

WITH DateRecursion AS (
    -- 初始行：生成第一个递增月的日期
    SELECT 
        ID,
        Date,
        DATEADD(MONTH, 1, Date) AS NewCol
    FROM YourTable
    WHERE DATEADD(MONTH, 1, Date) <= @EndDate
    UNION ALL
    -- 递归生成后续月份的日期
    SELECT 
        dr.ID,
        dr.Date,
        DATEADD(MONTH, 1, dr.NewCol) AS NewCol
    FROM DateRecursion dr
    WHERE DATEADD(MONTH, 1, dr.NewCol) <= @EndDate
)
SELECT ID, Date, NewCol
FROM DateRecursion
ORDER BY ID, NewCol;

代码解释 #

• 锚点成员：从原表取出每一行，计算Date加1个月的日期作为第一个NewCol，同时过滤掉加1个月后超过@EndDate的行，避免生成无效数据。
• 递归成员：基于上一次生成的NewCol继续加1个月，直到新日期超过@EndDate时停止递归。
• 最后查询递归CTE的结果，按ID和NewCol排序，就能得到目标格式的表格。

注意：把代码里的YourTable替换成你实际使用的表名即可。

内容的提问来源于stack exchange，提问作者Sally