SQL Server无需函数创建视图：按日期范围生成月度重复数据

完全可以不用创建自定义函数，用SQL Server原生的递归CTE或者系统表生成日期序列的方式就能实现。以下是两种可行方案，假设你的源表名为DateRangeTable，包含ID（行唯一标识）、StartDate、EndDate三个字段：

方案1：递归CTE实现（最直观） #

递归CTE不需要依赖任何额外对象，直接通过锚点+递归逻辑生成每月起始日：

CREATE VIEW MonthlyCheckpoints AS
WITH RecursiveDates AS (
    -- 锚点成员：获取每行的第一个检查点（StartDate所在月份的第一天）
    SELECT
        ID,
        StartDate,
        EndDate,
        DATEFROMPARTS(YEAR(StartDate), MONTH(StartDate), 1) AS CheckpointDate
    FROM DateRangeTable
    WHERE DATEFROMPARTS(YEAR(StartDate), MONTH(StartDate), 1) <= EndDate

    UNION ALL

    -- 递归成员：逐月生成下一个检查点，直到超过EndDate
    SELECT
        ID,
        StartDate,
        EndDate,
        DATEADD(MONTH, 1, CheckpointDate) AS CheckpointDate
    FROM RecursiveDates
    WHERE DATEADD(MONTH, 1, CheckpointDate) <= EndDate
)
SELECT
    ID,
    StartDate,
    EndDate,
    CheckpointDate
FROM RecursiveDates
ORDER BY ID, CheckpointDate
OPTION (MAXRECURSION 0); -- 解除递归深度限制（默认100，若日期范围超过100个月需要加这句）

说明： #

• 锚点成员先提取每行StartDate所在月份的第一天作为初始检查点，同时过滤掉初始检查点已经超过EndDate的行（避免无效递归）。
• 递归成员每次在上一个检查点基础上加1个月，直到生成的日期超过EndDate时停止。
• OPTION (MAXRECURSION 0)用于处理日期范围超过100个月的场景，若你的数据范围不会超过100个月，可以省略。

方案2：利用系统表生成日期序列（适合大数据量） #

如果源表数据量很大，递归CTE可能存在性能瓶颈，可以用系统表master.dbo.spt_values生成数字序列，再和源表交叉连接生成检查点：

CREATE VIEW MonthlyCheckpoints AS
SELECT
    dr.ID,
    dr.StartDate,
    dr.EndDate,
    DATEADD(MONTH, n.number, DATEFROMPARTS(YEAR(dr.StartDate), MONTH(dr.StartDate), 1)) AS CheckpointDate
FROM DateRangeTable dr
CROSS JOIN (
    -- 生成0到2047的数字序列（覆盖约170年的日期范围）
    SELECT number
    FROM master.dbo.spt_values
    WHERE type = 'P'
    AND number >= 0
) n
WHERE 
    DATEADD(MONTH, n.number, DATEFROMPARTS(YEAR(dr.StartDate), MONTH(dr.StartDate), 1)) <= dr.EndDate
ORDER BY dr.ID, CheckpointDate;

说明： #

• master.dbo.spt_values是SQL Server内置的系统表，其中type='P'的记录包含0到2047的连续数字，足够覆盖绝大多数业务场景的日期范围。
• 通过DATEADD基于初始月份第一天加对应数字的月份，生成所有符合条件的检查点，再通过WHERE子句筛选出在StartDate和EndDate范围内的日期。

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