MSSQL存储过程中如何动态生成过去14天日期序列实现空数据日计数补零

原代码无法执行的核心原因是T-SQL不允许在SELECT的列赋值表达式中嵌入WHILE这类流程控制语句，语法层面不支持这种写法。

方案1：递归CTE实现（推荐，适配存储过程场景） #

这种方式不需要额外声明循环变量，逻辑可直接嵌入存储过程，生成的日期序列可直接和业务表关联做统计。

• 优势：代码简洁，14天周期仅需13次递归，无额外性能损耗
• 适配场景：绝大多数MSSQL 2005及以上版本的存储过程开发

实现代码：

WITH DateSeq AS (
    -- 锚点：截取当前日期的纯日期部分，向前推13天即为14天周期起点
    SELECT CAST(DATEADD(day, -13, GETDATE()) AS date) AS [Date]
    UNION ALL
    -- 递归逐天累加，直到匹配到当天日期
    SELECT DATEADD(day, 1, [Date]) AS [Date]
    FROM DateSeq
    WHERE [Date] < CAST(GETDATE() AS date)
)
SELECT [Date]
INTO #Temp1
FROM DateSeq
OPTION (MAXRECURSION 14); -- 14天序列仅需13次递归，设置上限避免异常递归拖垮性能

*注意：如果需要排除当天、只统计过去14个完整自然日，只需将锚点的偏移量改为-14，递归终止条件改为[Date] < DATEADD(day, -1, CAST(GETDATE() AS date))即可。

方案2：系统数字表实现（无递归限制） #

针对不允许调整递归层数的旧版MSSQL环境，可以直接使用系统内置的master..spt_values数字辅助表生成序列，不需要写递归逻辑。

• 优势：不受递归层数参数限制，执行逻辑更稳定
• 适配场景：MSSQL 2000等无CTE支持的老旧环境

实现代码：

SELECT 
    CAST(DATEADD(day, -number, GETDATE()) AS date) AS [Date]
INTO #Temp1
FROM master..spt_values
WHERE type = 'P' 
  AND number BETWEEN 0 AND 13 -- 对应0到13天的偏移量，覆盖连续14天周期
ORDER BY number DESC

业务统计关联示例 #

生成的日期序列核心作用是补全无数据日期的计数缺口，关联业务表时需要注意两个关键点：

• 先将业务表的统计结果按纯日期格式分组，避免带时分秒的时间戳关联不上
• 用左连接保留全部日期序列，无匹配业务数据的位置用ISNULL将计数补为0

参考代码：

SELECT 
    t.[Date],
    ISNULL(biz.DailyOrderCount, 0) AS DailyOrderCount
FROM #Temp1 t
LEFT JOIN (
    -- 业务表按日期聚合的统计逻辑
    SELECT 
        CAST(CreateTime AS date) AS [Date],
        COUNT(OrderId) AS DailyOrderCount
    FROM Orders
    WHERE CreateTime >= DATEADD(day, -13, GETDATE())
    GROUP BY CAST(CreateTime AS date)
) biz ON t.[Date] = biz.[Date]
ORDER BY t.[Date] DESC

可直接调用的存储过程模板 #

直接复制代码后将业务表名、字段名替换为实际业务逻辑即可运行，示例内容：

CREATE OR ALTER PROCEDURE usp_GetPast14DaysOrderStat
AS
BEGIN
    -- 关闭多余的行数提示，避免影响调用方的返回结果
    SET NOCOUNT ON;

    -- 生成14天连续日期临时序列
    WITH DateSeq AS (
        SELECT CAST(DATEADD(day, -13, GETDATE()) AS date) AS [Date]
        UNION ALL
        SELECT DATEADD(day, 1, [Date])
        FROM DateSeq
        WHERE [Date] < CAST(GETDATE() AS date)
    )
    SELECT [Date]
    INTO #Temp1
    FROM DateSeq
    OPTION (MAXRECURSION 14);

    -- 关联业务表输出全日期范围的统计结果，无数据日期自动补0
    SELECT 
        t.[Date],
        ISNULL(biz.DailyOrderCount, 0) AS DailyOrderCount
    FROM #Temp1 t
    LEFT JOIN (
        SELECT 
            CAST(CreateTime AS date) AS [Date],
            COUNT(OrderId) AS DailyOrderCount
        FROM Orders
        WHERE CreateTime >= DATEADD(day, -13, GETDATE())
        GROUP BY CAST(CreateTime AS date)
    ) biz ON t.[Date] = biz.[Date]
    ORDER BY t.[Date] DESC;

    -- 清理临时表资源
    DROP TABLE #Temp1;
END

内容的提问来源于stack exchange，提问作者P. MAJ