SQL新增排除周末节假日的工作日计算列报错及实现方案咨询

问题1：计算列无法使用子查询的规避方案 #

SQL Server 不允许计算列中引用其他表的子查询，你可以选择以下任意一种方案规避：

• 方案1：使用标量自定义函数封装子查询逻辑
  先创建一个自定义函数统计指定日期区间的节假日数量，再在计算列中调用该函数：

-- 第一步：创建统计节假日数量的函数
CREATE FUNCTION dbo.GetHolidayCount(@StartDate DATE, @EndDate DATE)
RETURNS INT
AS
BEGIN
    DECLARE @Count INT
    SELECT @Count = COUNT(*) FROM Holidays 
    WHERE Holidays >= @StartDate AND Holidays < @EndDate
    RETURN @Count
END
GO
-- 第二步：新增计算列
USE DATABASE
GO
ALTER TABLE [dbo].[TABLE1]
ADD Loading_LT AS (
    DATEDIFF(dd,[OrderDate],[ProcessedDate])
    - DATEDIFF(ww,[OrderDate],[ProcessedDate]) * 2
    - CASE WHEN DATENAME(dw,[OrderDate]) = 'Sunday' THEN 1 ELSE 0 END
    - CASE WHEN DATENAME(dw,[ProcessedDate]) = 'Saturday' THEN 1 ELSE 0 END
    - dbo.GetHolidayCount([OrderDate], [ProcessedDate])
)
GO

注意：该方案的计算列无法设置为持久化或建索引，每次查询时会实时计算，适合数据量较小的场景。

• 方案2：使用触发器维护实体存储列
  直接新增普通字段存储计算结果，通过INSERT、UPDATE触发器在数据写入/变更时自动更新字段值：

-- 第一步：新增普通字段
ALTER TABLE [dbo].[TABLE1] ADD Loading_LT INT
GO
-- 第二步：创建触发器（示例为INSERT+UPDATE触发器，需替换代码中的主键字段）
CREATE TRIGGER trg_CalculateLoadingLT ON [dbo].[TABLE1]
AFTER INSERT, UPDATE
AS
BEGIN
    UPDATE t
    SET Loading_LT = (
        DATEDIFF(dd,i.OrderDate,i.ProcessedDate)
        - DATEDIFF(ww,i.OrderDate,i.ProcessedDate) * 2
        - CASE WHEN DATENAME(dw,i.OrderDate) = 'Sunday' THEN 1 ELSE 0 END
        - CASE WHEN DATENAME(dw,i.ProcessedDate) = 'Saturday' THEN 1 ELSE 0 END
        - (SELECT COUNT(*) FROM Holidays WHERE Holidays >= i.OrderDate AND Holidays < i.ProcessedDate)
    )
    FROM [dbo].[TABLE1] t
    INNER JOIN inserted i ON t.主键字段 = i.主键字段
END
GO

该方案的字段为实际存储值，查询性能更高，支持建索引，适合数据量较大的场景。

• 方案3：通过视图封装计算逻辑
  不修改原表结构，创建视图实现计算逻辑，后续查询直接访问视图即可。

问题2：方案合理性及替代方案 #

新增表列方案的合理性 #

该方案是合理的，计算逻辑统一维护在SQL层，Power BI、Excel拉取数据时直接读取字段即可，避免多端重复开发逻辑、出现计算结果不一致的问题。

其他适配Power BI/Excel场景的替代方案 #

• Power BI端DAX计算：如果你的Holidays表也会同步到Power BI数据集，可直接在Power BI中新增计算列，用DAX自带的NETWORKDAYS函数实现，代码更简洁，对新手更友好：

  Loading_LT = NETWORKDAYS([OrderDate], [ProcessedDate], 1, 'Holidays'[Holidays])
  

• Excel端计算：如果仅用Excel拉取数据使用，可将节假日列表同步到Excel Sheet中，直接用Excel自带的NETWORKDAYS函数计算即可。

内容的提问来源于stack exchange，提问作者Martín Cabrera