如何按日期范围生成personId与日期的关联行记录

问题描述 #

我有一张包含personIds、startDate和endDate字段的表，需要查询生成每个personId-date关联对，日期范围由startDate和endDate两列指定。

输入表数据：

personId | startDate | endDate
1        | 2018-05-10| 2018-05-13

期望输出：

personId | date
1        | 2018-05-10
1        | 2018-05-11
1        | 2018-05-12
1        | 2018-05-13

这个需求属于典型的日期范围拆分行场景，下面是几个常用数据库的具体解法，你可以根据自己的环境选择：

1. MySQL 实现 #

MySQL 8.0+ 支持递归CTE，是最方便的实现方式；低版本则可以用数字序列表来关联。

方法1：递归CTE（推荐，MySQL 8.0+） #

WITH RECURSIVE date_range AS (
    -- 初始行：取每个person的startDate
    SELECT personId, startDate AS date, endDate
    FROM your_table
    UNION ALL
    -- 递归：每天加1天，直到等于endDate
    SELECT personId, DATE_ADD(date, INTERVAL 1 DAY), endDate
    FROM date_range
    WHERE date < endDate
)
SELECT personId, date
FROM date_range
ORDER BY personId, date;

方法2：数字序列表（兼容MySQL 5.x） #

如果你的MySQL版本不支持递归，可以先创建一个包含连续数字的表（覆盖你需要的最大日期跨度），再关联查询：

-- 先创建并填充数字表（一次性操作）
CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-- 重复执行几次，让数字足够多（比如到1000天）
INSERT INTO numbers SELECT n+10 FROM numbers;
INSERT INTO numbers SELECT n+20 FROM numbers;

-- 关联生成日期序列
SELECT 
    t.personId,
    DATE_ADD(t.startDate, INTERVAL n.n DAY) AS date
FROM your_table t
JOIN numbers n ON DATE_ADD(t.startDate, INTERVAL n.n DAY) <= t.endDate
ORDER BY t.personId, date;

2. PostgreSQL 实现 #

PostgreSQL 自带generate_series函数，能直接生成日期序列，写法非常简洁：

SELECT 
    t.personId,
    generate_series(t.startDate, t.endDate, '1 day'::interval)::date AS date
FROM your_table t
ORDER BY personId, date;

3. SQL Server 实现 #

SQL Server 同样支持递归CTE，注意如果日期跨度超过100天，需要加上递归次数限制的选项：

WITH date_range AS (
    SELECT personId, startDate AS date, endDate
    FROM your_table
    UNION ALL
    SELECT personId, DATEADD(DAY, 1, date), endDate
    FROM date_range
    WHERE date < endDate
)
SELECT personId, date
FROM date_range
ORDER BY personId, date
OPTION (MAXRECURSION 0); -- 取消默认的100次递归限制，适用于长日期范围

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