基于SiteSlot表的起止日期将单条记录拆分为每日多条记录

下面针对主流数据库给出具体实现方案，核心思路是生成原记录起止日期范围内的所有日期，再与原表关联生成每日记录。

1. PostgreSQL #

PostgreSQL自带generate_series函数，能快速生成日期序列，直接关联原表即可：

SELECT
    s.id,
    s.site_id,
    generate_series(s.start_date, s.end_date, interval '1 day')::date AS slot_date,
    s.slot_value,
    -- 按需保留原表其他字段
    s.other_columns
FROM SiteSlot s;

如果原表的start_date/end_date是带时间的timestamp类型，::date会自动截断为日期部分。

2. SQL Server #

使用递归CTE生成日期范围，再与原表关联：

WITH DateSeries AS (
    SELECT start_date AS slot_date, end_date, id, site_id, slot_value, other_columns
    FROM SiteSlot
    UNION ALL
    SELECT DATEADD(day, 1, slot_date), end_date, id, site_id, slot_value, other_columns
    FROM DateSeries
    WHERE slot_date < end_date
)
SELECT id, site_id, slot_date, slot_value, other_columns
FROM DateSeries
ORDER BY id, slot_date
OPTION (MAXRECURSION 0); -- 解除递归次数限制，支持超过100天的跨期记录

3. MySQL 8.0+ #

通过递归CTE实现（MySQL 8.0及以上版本支持递归语法）：

WITH RECURSIVE DateSeries AS (
    SELECT start_date AS slot_date, end_date, id, site_id, slot_value, other_columns
    FROM SiteSlot
    UNION ALL
    SELECT DATE_ADD(slot_date, INTERVAL 1 DAY), end_date, id, site_id, slot_value, other_columns
    FROM DateSeries
    WHERE slot_date < end_date
)
SELECT id, site_id, slot_date, slot_value, other_columns
FROM DateSeries
ORDER BY id, slot_date;

示例说明 #

假设原表SiteSlot有如下记录：

执行上述SQL后，输出结果为：

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