SQL如何为每条含起止日期的记录生成日期间隔内的所有对应日期行

需求说明 #

现有存储员工日期区间的表，包含EmpID（员工ID）、StartDate（区间起始日期）、EndDate（区间结束日期）三个字段，需要将每条员工记录按[StartDate, EndDate]区间包含的所有日期拆分，每个日期对应生成一条独立的员工记录。

测试数据构建 #

DROP TABLE IF EXISTS #Emp
CREATE TABLE #Emp
(
    EmpID VARCHAR(6), 
    StartDate DATE, 
    EndDate DATE
)
INSERT INTO #Emp 
VALUES ('E001','2021-08-01', '2021-08-05'),
       ('E002','2021-08-22', '2021-08-22'),
       ('E003','2021-08-15', '2021-08-17')

实现方案（适配SQL Server） #

方案1：递归CTE实现（无需额外表依赖） #

适合小数据量、临时查询场景，无需预先构建辅助表：

WITH DateCTE AS (
    -- 递归起始节点：取每个员工的起始日期
    SELECT EmpID, StartDate AS WorkDate, EndDate
    FROM #Emp
    UNION ALL
    -- 递归迭代：每次日期加1天，直到达到结束日期
    SELECT EmpID, DATEADD(DAY, 1, WorkDate), EndDate
    FROM DateCTE
    WHERE WorkDate < EndDate
)
SELECT EmpID, WorkDate
FROM DateCTE
ORDER BY EmpID, WorkDate
-- 解除默认100层递归限制，支持超过100天的长区间拆分
OPTION (MAXRECURSION 0);

方案2：数字辅助表实现（性能更优） #

适合大数据量场景，需预先构建存储连续整数的数字辅助表（表名Numbers，字段Num存储0、1、2...的连续数值）：

SELECT e.EmpID, DATEADD(DAY, n.Num, e.StartDate) AS WorkDate
FROM #Emp e
INNER JOIN Numbers n 
    ON n.Num <= DATEDIFF(DAY, e.StartDate, e.EndDate)
ORDER BY e.EmpID, WorkDate

输出效果 #

使用测试数据运行后得到如下结果：

如使用MySQL、PostgreSQL等其他数据库，仅需调整对应日期函数、递归语法即可，核心拆分逻辑一致。

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