MYSQL 不依赖其他表构建日期范围内的连续月份或日期

--  指定时间内的连续日期

-- 定义自增变量
SET @i = -1;
-- 指定日期范围
SET @sdt = '2015-08-01';
SET @edt = '2015-08-31';

-- 创建指定日期间记录数天数需要 加 1
SET @sql = REPEAT(" select 1 as tid union all", DATEDIFF(@edt, @sdt)+1);
-- 将最后一个union all 去掉
SET @sql = LEFT(@sql, LENGTH(@sql) - LENGTH("union all"));
-- 拼接sql
SET @sql = CONCAT("select date_add('", @sdt, "', interval @i:=@i+1 day) as date from (",@sql,") as tmp");
PREPARE stmt FROM @sql;
EXECUTE stmt;

--  指定时间内的连续月份

-- 定义自增变量
SET @i = -1;
-- 指定日期范围
SET @sdt = '2015-01-01';
SET @edt = '2015-12-31';

-- 创建指定月份间记录
SET @sql = REPEAT(" select 1 as tid union all",
(SELECT
PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM @edt),
EXTRACT(YEAR_MONTH FROM @sdt))+1));
-- 将最后一个union all 去掉
SET @sql = LEFT(@sql,LENGTH(@sql)-LENGTH(" union all"));
-- 拼接sql
SET @sql = CONCAT("select 1 AS tid, date_add('",@sdt,"', interval @i:=@i+1 MONTH) as mh 
FROM (",@sql,") AS t");
PREPARE stmt FROM @sql;
EXECUTE stmt;

应用示例：

-- 创建基础资料表

CREATE TABLE `contract` (
  `id` int(11) DEFAULT NULL,
  `startday` datetime DEFAULT NULL,
  `endday` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

INSERT INTO contract (id, startday, endday) 
VALUES('1', '2014-01-01 00:00:00', '2015-12-31 00:00:00');
INSERT INTO contract (id, startday, endday) 
VALUES('2', '2012-01-01 00:00:00', '2015-12-31 00:00:00');

-- 创建资料月明细表

CREATE TABLE `finance` (
  `id` int(11) DEFAULT NULL,
  `contract_id` int(11) DEFAULT NULL,
  `targetmonth` datetime DEFAULT NULL,
  `money` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

INSERT INTO myTmpDB.finance (id, contract_id, targetmonth, money) 
VALUES('1','1','2015-06-16 16:52:15','50') ;
INSERT INTO myTmpDB.finance (id, contract_id, targetmonth, money) 
VALUES('2','1','2015-07-16 16:52:15','60') ;
INSERT INTO myTmpDB.finance (id, contract_id, targetmonth, money) 
VALUES('3','2','2015-01-16 16:52:15','80') ;

--  创建存储过程

DELIMITER $$

DROP PROCEDURE IF EXISTS p_contract $$

CREATE PROCEDURE p_contract(IN p_cid INT)
BEGIN
-- 指定合同编号
SET @id = p_cid;
-- 定义自增变量
SET @i = -1;
-- 定义报表日期区间
SET @sdt = (SELECT startday FROM contract WHERE id = @id);
SET @edt = (SELECT CASE WHEN endday > NOW() THEN NOW() 
ELSE endday END FROM contract WHERE id = @id);
-- 指定日期间月份数
SET @cnt =((SELECT
PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM 
CASE WHEN endday > NOW() THEN NOW() ELSE endday END),
EXTRACT(YEAR_MONTH FROM startday))
FROM contract WHERE id = @id)+1);
-- 创建期间记录
SET @sql1 = REPEAT(" select 1 as tid union all", @cnt);
SET @sql1 = LEFT(@sql1, LENGTH(@sql1)-LENGTH(" union all"));
-- 拼接sql
SET @sql2 = CONCAT("select DATE_FORMAT(DATE_ADD(startday, 
 interval @i:=@i+1 MONTH), '%Y-%m')AS yrmh,
 t1.id, t1.startday, t1.endday 
 FROM contract AS t1
 INNER JOIN(",@sql1,") AS t2
 WHERE t1.id ='", @id, "'");


SET @sql3 = CONCAT("
 SELECT tt1.id, tt1.startday, tt1.endday, tt1.yrmh,
 IFNULL(tt2.money,0) AS money FROM 
 (", @sql2,")as tt1
 LEFT JOIN finance AS tt2
 ON tt1.id = tt2.contract_id 
 and tt1.yrmh = DATE_FORMAT(tt2.targetmonth, '%Y-%m') 
 order by tt1.yrmh ");
PREPARE stmt FROM @sql3;
EXECUTE stmt;
END$$

-- 存储过程调用：

CALL p_contract(1)