如何在SQL中按订单号填充日期区间内的所有日期？

原始订单表 #

期望生成的每日记录 #

问题与报错 #

需要把每个订单From到To区间内的所有日期拆成每日记录，但不知道怎么按OrderNumber分区生成日期。试了下面的SQL，结果报**Invalid Object Name 'PatientDays'**错误：

SELECT PatientDays.OrderNumber,
       [Date] = DATEADD(DAY, T.N, PatientDays.From)
FROM (
    SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
    FROM sys.objects
) AS T
JOIN PatientDays AS PatientDays
    ON DATEDIFF(DAY, PatientDays.From, PatientDays.To) >= T.N;

解决方案 #

1. 先解决报错问题 #

报错是因为代码里写的表名PatientDays和你实际的订单表名不匹配，把PatientDays换成你自己的订单表名就行（比如假设你的表叫Orders）。

2. 推荐方案：递归CTE（适合SQL Server、MySQL 8.0+、PostgreSQL等） #

用递归CTE生成日期区间逻辑更直观，不需要依赖系统表，代码如下：

WITH DateRange AS (
    -- 第一步：取原始订单数据，初始化每日日期为From
    SELECT 
        OrderNumber,
        [From],
        [To],
        DailyDate = [From]
    FROM Orders -- 替换成你的实际表名
    UNION ALL
    -- 第二步：递归生成后续日期，直到DailyDate等于To
    SELECT 
        dr.OrderNumber,
        dr.[From],
        dr.[To],
        DailyDate = DATEADD(DAY, 1, dr.DailyDate)
    FROM DateRange dr
    WHERE dr.DailyDate < dr.[To]
)
-- 输出结果，按订单号和日期排序
SELECT 
    OrderNumber,
    [From],
    [To],
    DailyDate
FROM DateRange
ORDER BY OrderNumber, DailyDate
OPTION (MAXRECURSION 0); -- 如果订单日期区间超过100天，必须加这个取消递归次数限制

3. 备选方案：数字辅助表（适合不支持递归CTE的旧版数据库） #

如果你的数据库不支持递归CTE（比如MySQL 5.x），可以先生成足够多的数字序列，再关联订单表生成日期：

-- 生成0到99的数字序列，可根据最大日期区间扩展数量
WITH Numbers AS (
    SELECT 0 AS N
    UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
    UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
    -- 按需添加更多数字，确保覆盖最长的日期差
)
SELECT 
    o.OrderNumber,
    o.[From],
    o.[To],
    DATEADD(DAY, n.N, o.[From]) AS DailyDate
FROM Orders o -- 替换成你的实际表名
JOIN Numbers n 
    ON DATEDIFF(DAY, o.[From], o.[To]) >= n.N
ORDER BY o.OrderNumber, DailyDate;

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