根据日期部分将行取消分组为多行 [英] Un-group a row into many rows based on date parts

问题描述

我正在使用MySql，并且我有一个带有StartDate和EndDate列的Policy表.

I'm using MySql and I have a Policy table with StartDate and EndDate columns.

如何编写SELECT查询以在这两列之间的日期范围内为每个月给我一个新行.

How can I write a SELECT query to give me a new row for each month in the date range between these 2 columns.

例如，如果我有以下政策:

For example if I have a policy with:

Id        StartDate        EndDate
123456    2011-05-25       2011-07-26

我想看看:

Id       PolicyId        StartDate        EndDate
1        123456          2011-05-25       2011-06-24
2        123456          2011-06-25       2011-07-24
3        123456          2011-07-25       2011-07-26

推荐答案

我不确定性能，因为我对存储过程没有太多经验，因此可能会有更好的方法.另外，您可能想更改临时表的结构(又名PolicyList).反正...

I'm not sure about performance because I'm not much experienced with stored procedures so there might be a better approach. Also, you might want to change the structure of the temporary table (aka. PolicyList). Anyway…

也可以将其转换为之前/之后的触发器，而不是每次都执行.

This can also be converted into before/after triggers instead of executing it each time.

DROP PROCEDURE IF EXISTS CreatePolicyList;

DELIMITER //
CREATE PROCEDURE CreatePolicyList()
BEGIN
    DECLARE origId, done INT DEFAULT 0;
    DECLARE startD, endD DATE;

    DECLARE cur CURSOR FOR
        SELECT id, StartDate, EndDate FROM Policy;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    DROP TEMPORARY TABLE IF EXISTS PolicyList;
    CREATE TEMPORARY TABLE PolicyList (
        id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        PolicyId INT(11) NOT NULL,
        StartDate DATE NOT NULL,
        EndDate DATE NOT NULL,
        PRIMARY KEY (id)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    OPEN cur;
    recLoop: LOOP
        FETCH cur INTO origId, startD, endD;
        IF (done)
            THEN LEAVE recLoop;
        END IF;

        -- following is an alternative to keep records like
        -- "2011-05-25, 2011-06-25" in a single record
        -- WHILE startD < DATE_SUB(endD, INTERVAL 1 MONTH) DO
        WHILE startD < DATE_ADD(DATE_SUB(endD, INTERVAL 1 MONTH), INTERVAL 1 DAY) DO
            INSERT INTO PolicyList (PolicyId, StartDate, EndDate)
            VALUES (origId, startD,DATE_SUB(
                DATE_ADD(startD, INTERVAL 1 MONTH),
                INTERVAL 1 DAY
            ));
            SET startD = DATE_ADD(startD, INTERVAL 1 MONTH);
        END WHILE;

        IF startD >= DATE_SUB(endD, INTERVAL 1 MONTH) THEN
            INSERT INTO PolicyList (PolicyId, StartDate, EndDate)
            VALUES (origId, startD, endD);
        END IF;
    END LOOP;
    CLOSE cur;

END //

CALL CreatePolicyList;

然后查询:

SELECT * FROM PolicyList
ORDER BY PolicyId, StartDate;

这篇关于根据日期部分将行取消分组为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！