SQL中如何将包含跨月起止日期的单行记录拆分为多行

核心逻辑 #

• 生成覆盖所有待拆分记录涉及月份的连续月份序列
• 将原表与月份序列关联，保留原区间与对应月份存在重叠的关联结果
• 逐行计算拆分后的起止日期：
  • 拆分后起始日期：取原Startdate与当月第一天的较大值
  • 拆分后结束日期：取原Enddate与当月最后一天的较小值

示例实现（以PostgreSQL为例） #

WITH sample_data AS (
    -- 替换为你的原始数据表
    SELECT 
        'XXXX' AS value,
        '2024-07-02'::DATE AS startdate,
        '2024-08-16'::DATE AS enddate
),
-- 生成连续月份序列，范围覆盖原始数据的最小起始到最大结束日期
month_series AS (
    SELECT generate_series(
        (SELECT DATE_TRUNC('month', MIN(startdate)) FROM sample_data),
        (SELECT DATE_TRUNC('month', MAX(enddate)) FROM sample_data),
        INTERVAL '1 month'
    ) AS month_start
)
SELECT 
    s.value,
    -- 计算拆分后的起始日期
    GREATEST(s.startdate, m.month_start::DATE) AS startdate,
    -- 计算拆分后的结束日期
    LEAST(s.enddate, (m.month_start + INTERVAL '1 month - 1 day')::DATE) AS enddate
FROM sample_data s
JOIN month_series m 
    ON m.month_start <= s.enddate 
    AND (m.month_start + INTERVAL '1 month - 1 day') >= s.startdate;

示例实现（以MySQL 8.0+为例） #

WITH RECURSIVE sample_data AS (
    -- 替换为你的原始数据表
    SELECT 'XXXX' AS value, '2024-07-02' AS startdate, '2024-08-16' AS enddate
),
month_series AS (
    -- 递归生成连续月份序列
    SELECT DATE_FORMAT((SELECT MIN(startdate) FROM sample_data), '%Y-%m-01') AS month_start
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH) 
    FROM month_series
    WHERE month_start < (SELECT DATE_FORMAT(MAX(enddate), '%Y-%m-01') FROM sample_data)
)
SELECT 
    s.value,
    GREATEST(s.startdate, m.month_start) AS startdate,
    LEAST(s.enddate, LAST_DAY(m.month_start)) AS enddate
FROM sample_data s
JOIN month_series m
    ON m.month_start <= s.enddate
    AND LAST_DAY(m.month_start) >= s.startdate;

注：如果你的日期字段存储的是2.July这类非标准字符串格式，需要先转为标准日期类型再做运算：

• PostgreSQL 转换函数：TO_DATE(日期字段, 'DD.Month')
• MySQL 转换函数：STR_TO_DATE(日期字段, '%d.%M')
  拆分完成后如果需要转回原字符串格式，使用对应数据库的日期格式化函数处理即可。

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