如何用SQL Cross Join生成各ID日期范围内的所有日期？

嘿，这个需求我经常遇到，用递归CTE就能轻松搞定，比单纯Cross Join更适合处理多行数据的场景，我给你详细讲讲两种可行的方法：

方法一：递归CTE（适配SQL Server、PostgreSQL、MySQL 8.0+） #

递归CTE可以自动为每一行ID生成日期范围内的所有日期，完全不用手动处理单行数据，是最灵活的方案。

示例代码 #

假设你的表名为DateRanges，结构是ID, StartDate, EndDate，直接用下面的SQL就能得到结果：

WITH DateCTE AS (
    -- 锚点：先取出每一行的起始日期
    SELECT 
        ID, 
        StartDate AS CurrentDate, 
        EndDate
    FROM DateRanges
    UNION ALL
    -- 递归：每天给日期加1，直到达到结束日期
    SELECT 
        ID, 
        DATEADD(day, 1, CurrentDate) AS CurrentDate, 
        EndDate
    FROM DateCTE
    WHERE CurrentDate < EndDate
)
SELECT ID, CurrentDate AS Date
FROM DateCTE
ORDER BY ID, CurrentDate;

逻辑说明 #

• 锚点成员先把原表的每一行StartDate作为起始日期拉出来
• 递归成员会逐天给CurrentDate加1，直到它等于EndDate，这样就自动生成了每个ID从开始到结束的所有日期
• 最后查询CTE就能得到你要的结果，不管原表有多少行ID，都会自动处理

方法二：日期维度表+Cross Join（适配所有支持Cross Join的数据库） #

如果你的数据库版本不支持递归CTE，也可以提前准备一个日期维度表，再用Cross Join关联筛选。

步骤说明 #

1. 先创建一个日期维度表Dates，里面有一列CalendarDate，包含你业务需要的所有日期范围（比如从2000年到2030年的所有日期）
2. 执行关联查询：

SELECT 
    dr.ID, 
    d.CalendarDate
FROM DateRanges dr
CROSS JOIN Dates d
WHERE d.CalendarDate BETWEEN dr.StartDate AND dr.EndDate
ORDER BY dr.ID, d.CalendarDate;

注意事项 #

• 日期维度表需要提前维护，确保覆盖所有可能的日期范围
• 这种方法在数据量大的时候性能可能不如递归CTE，因为Cross Join会先生成大量中间数据，再做筛选

测试你的示例数据 #

假设原表有一行数据：

用上面任意一种方法，输出都会是：

如果原表有多行数据（比如ID=2的日期范围是04/01/2018到05/01/2018），结果会自动包含ID=2的两行日期，完全满足多行处理的需求。

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