如何使用MS SQL将项目按指定日期范围拆分为对应工作日记录？

现有方案的问题 #

你当前用sys.columns生成临时日期序列的方案确实能实现需求，但存在几个明显缺陷：

• 日期序列长度受sys.columns表行数限制，如果你的项目时间跨度更大可能会出现日期缺失
• 每次查询都要动态生成日期序列，执行效率低
• 用datename判断周几的逻辑受数据库语言环境影响，要是实例语言设为中文，返回的星期值是「星期一」这类中文，你的过滤条件会直接失效
• 无法支持法定节假日的过滤需求

推荐最优方案：使用持久化日期维度表 #

这是生产环境最常用的处理日期类需求的方案，一次维护全场景复用：

1. 构建日期维度表 #

你可以直接新建一张专门存储日期属性的维度表，结构参考如下（适配SQL Server语法，和你现有技术栈兼容）：

-- 创建日期维度表
CREATE TABLE dim_date (
    dt DATE PRIMARY KEY CLUSTERED,
    year INT NOT NULL,
    month INT NOT NULL,
    day INT NOT NULL,
    weekday_no INT NOT NULL, -- 周几，1=周一，7=周日
    is_weekend TINYINT NOT NULL DEFAULT 0, -- 1=周末，0=周内
    is_holiday TINYINT NOT NULL DEFAULT 0, -- 1=法定节假日，0=非节假日
    holiday_name NVARCHAR(50) NULL -- 节假日名称
);

2. 初始化日期数据 #

一次性插入你需要的时间范围内的所有日期：

DECLARE @start_dt DATE = '2020-01-01', @end_dt DATE = '2030-12-31';
WHILE @start_dt <= @end_dt
BEGIN
    INSERT INTO dim_date (dt, year, month, day, weekday_no, is_weekend)
    VALUES (
        @start_dt,
        YEAR(@start_dt),
        MONTH(@start_dt),
        DAY(@start_dt),
        (DATEPART(WEEKDAY, @start_dt) + @@DATEFIRST - 2) % 7 + 1, -- 统一让周一=1，不受@@DATEFIRST设置影响
        CASE WHEN (DATEPART(WEEKDAY, @start_dt) + @@DATEFIRST - 2) % 7 + 1 IN (6,7) THEN 1 ELSE 0 END
    );
    SET @start_dt = DATEADD(DAY, 1, @start_dt);
END

3. 维护节假日 #

每年国家发布法定节假日安排后，批量更新is_holiday字段即可，示例：

-- 2024年元旦节假日更新
UPDATE dim_date SET is_holiday = 1, holiday_name = '元旦' WHERE dt = '2024-01-01';

4. 简化你的拆分查询 #

原来的复杂逻辑可以简化为如下代码，效率更高可读性更强：

SELECT 
    p.*,
    dd.dt AS work_date
FROM 项目表 p
LEFT JOIN dim_date dd
    ON dd.dt BETWEEN CAST(p.start_date AS DATE) AND CAST(p.end_date AS DATE)
    AND dd.is_weekend = 0
    AND dd.is_holiday = 0

临时方案：用递归CTE生成日期序列 #

如果你暂时不想建持久化表，也可以用递归CTE代替sys.columns生成日期序列，稳定性更高，不会出现行数不足的问题：

WITH date_series AS (
    SELECT CAST('2020-01-01' AS DATE) AS dt
    UNION ALL
    SELECT DATEADD(DAY, 1, dt) FROM date_series WHERE dt < '2030-12-31'
)
SELECT 
    p.*,
    dd.dt AS work_date
FROM 项目表 p
LEFT JOIN date_series dd
    ON dd.dt BETWEEN CAST(p.start_date AS DATE) AND CAST(p.end_date AS DATE)
    AND (DATEPART(WEEKDAY, dd.dt) + @@DATEFIRST - 2) % 7 + 1 BETWEEN 1 AND 5
OPTION (MAXRECURSION 0); -- 取消递归层数限制，避免日期跨度大报错

该方案还是无法解决节假日过滤的问题，只适合临时使用。

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