问题描述
我正在使用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;
这篇关于根据日期部分将行取消分组为多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!