SQL中无需循环计算指定工作日数后未来日期的高效方法

你的循环实现逻辑直观，但当@daystoadd数值较大时，会产生大量重复查询和循环迭代，性能损耗明显。下面是基于集合运算的无循环优化方案，利用SQL Server的CTE（公共表表达式）生成日期序列，结合非工作日表快速定位目标日期：

优化后的函数代码 #

CREATE FUNCTION [dbo].[AddWorkingDays]
(
    @originaldate DATE,
    @daystoadd INT
)
RETURNS DATE
AS
BEGIN
    -- 处理@daystoadd为0的边界情况
    IF @daystoadd = 0
        RETURN @originaldate;

    DECLARE @targetDate DATE;

    -- 用递归CTE生成符合要求的工作日序列，取第@daystoadd个工作日
    WITH DateSequence AS
    (
        SELECT 
            1 AS DayCount,
            DATEADD(DAY, 1, @originaldate) AS CurrentDate
        WHERE 
            DATEADD(DAY, 1, @originaldate) NOT IN (SELECT [date-off] FROM [Non-working-days-table])
        
        UNION ALL
        
        SELECT 
            ds.DayCount + 1,
            DATEADD(DAY, 1, ds.CurrentDate)
        FROM DateSequence ds
        WHERE 
            ds.DayCount < @daystoadd
            AND DATEADD(DAY, 1, ds.CurrentDate) NOT IN (SELECT [date-off] FROM [Non-working-days-table])
    )
    SELECT @targetDate = CurrentDate
    FROM DateSequence
    WHERE DayCount = @daystoadd;

    -- 处理连续非工作日导致递归序列未覆盖的极端情况
    IF @targetDate IS NULL
    BEGIN
        -- 生成足够长的日期序列，筛选出第N个工作日
        DECLARE @estimatedDays INT = @daystoadd + (SELECT COUNT(*) FROM [Non-working-days-table] WHERE [date-off] BETWEEN @originaldate AND DATEADD(DAY, @daystoadd, @originaldate)) + 10;
        
        WITH ExtendedDateSequence AS
        (
            SELECT 
                CASE WHEN DATEADD(DAY, n.Number, @originaldate) NOT IN (SELECT [date-off] FROM [Non-working-days-table]) THEN 1 ELSE 0 END AS IsWorkDay,
                DATEADD(DAY, n.Number, @originaldate) AS CurrentDate
            FROM 
                (SELECT TOP (@estimatedDays) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Number FROM sys.all_columns) n
        )
        SELECT @targetDate = CurrentDate
        FROM ExtendedDateSequence
        WHERE IsWorkDay = 1
        ORDER BY CurrentDate
        OFFSET @daystoadd ROWS FETCH NEXT 1 ROW ONLY;
    END

    RETURN @targetDate;
END

方案优势 #

• 无循环迭代：利用CTE的集合运算一次性完成日期筛选，避免了循环中重复查询非工作日表的开销。
• 性能提升：若Non-working-days-table的date-off字段建立索引，非工作日查询速度会大幅提升。
• 边界覆盖：处理了@daystoadd=0、连续非工作日等特殊场景，避免返回无效值。

如果你的非工作日表数据相对固定，还可以用数学估算+校验的方案进一步优化：先预估初始目标日期，统计这段区间的非工作日数量并叠加，再校验新日期区间的非工作日数量，直到结果稳定——这种方法的迭代次数远少于原循环，适合非工作日较少的场景。

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