如何在MySQL中按日期范围为每条数据生成每月一行

需求与示例 #

输入数据 #

Name  | Value       | startdate   | enddate
1     | 10          | 2023-04-01  | 2023-06-30
2     | 99          | 2023-03-01  | 2023-05-01

预期输出 #

Name  | Value       | date
1     | 10          | 2023-04
1     | 10          | 2023-05
1     | 10          | 2023-06
2     | 99          | 2023-03
2     | 99          | 2023-04
2     | 99          | 2023-05

需求明确：将每条数据按其startdate到enddate的所有月份拆分生成新行，保留Name和Value字段，日期格式统一为YYYY-MM，需支持跨年度日期范围（如2022-12到2023-03这类跨年场景）。

现有问题 #

曾尝试相关方案未得到预期结果，且跨年度场景下部分方案失效；当前编写的SQL卡在分组逻辑，无法完成拆分：

SELECT
    value,
    DATE_FORMAT(startdate, "%Y-%m") AS startMonth,
    DATE_FORMAT(enddate, "%Y-%m") AS endMonth
FROM
    table
GROUP BY
    -- 此处无法确定分组逻辑

解决方案 #

方案1：递归CTE实现（MySQL 8.0+ 推荐） #

利用递归公共表表达式生成所有需要的月度数据，再与原表关联过滤，天然支持跨年度：

WITH RECURSIVE date_range AS (
    -- 锚点成员：取每条数据的起始月份
    SELECT 
        Name,
        Value,
        DATE_FORMAT(startdate, '%Y-%m') AS current_month,
        DATE_FORMAT(enddate, '%Y-%m') AS end_month
    FROM your_table
    UNION ALL
    -- 递归成员：逐月生成直到结束月份
    SELECT 
        Name,
        Value,
        DATE_FORMAT(STR_TO_DATE(current_month, '%Y-%m') + INTERVAL 1 MONTH, '%Y-%m'),
        end_month
    FROM date_range
    WHERE current_month < end_month
)
SELECT Name, Value, current_month AS date
FROM date_range
ORDER BY Name, date;

方案2：存储过程实现（兼容低版本MySQL） #

通过循环生成月度数据存入临时表，再与原表关联：

DELIMITER //
CREATE PROCEDURE SplitMonthlyData()
BEGIN
    -- 创建临时表存储结果
    DROP TABLE IF EXISTS temp_monthly_data;
    CREATE TEMPORARY TABLE temp_monthly_data (
        Name INT,
        Value INT,
        date VARCHAR(7)
    );
    
    -- 声明变量
    DECLARE done INT DEFAULT 0;
    DECLARE v_name INT;
    DECLARE v_value INT;
    DECLARE v_start DATE;
    DECLARE v_end DATE;
    DECLARE v_current DATE;
    
    -- 游标遍历原表数据
    DECLARE cur CURSOR FOR 
        SELECT Name, Value, startdate, enddate FROM your_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_name, v_value, v_start, v_end;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 初始化当前月份为起始日期的月初
        SET v_current = DATE_FORMAT(v_start, '%Y-%m-01');
        
        -- 循环生成每个月份直到结束日期的月末
        WHILE v_current <= LAST_DAY(v_end) DO
            INSERT INTO temp_monthly_data 
            VALUES (v_name, v_value, DATE_FORMAT(v_current, '%Y-%m'));
            SET v_current = v_current + INTERVAL 1 MONTH;
        END WHILE;
    END LOOP;
    CLOSE cur;
    
    -- 查询结果
    SELECT * FROM temp_monthly_data ORDER BY Name, date;
END //
DELIMITER ;

-- 调用存储过程
CALL SplitMonthlyData();

说明 #

• 递归CTE方案代码简洁，性能更优，适合MySQL 8.0及以上版本；
• 存储过程方案兼容MySQL 5.x等低版本，逻辑直观但性能略逊；
• 替换代码中的your_table为实际表名即可使用。

内容的提问来源于stack exchange，提问作者William Brochensque junior