在管理系统开发过程中，经常会按照月份统计系统数据，并生成报表。常用的做法就是写代码生成月份一条条地在数据库中查询结果，并组装反馈到前台。
经过多次百度后，结合自己的理解，将生成这个的SQL语句写成存储过程，记录在此备忘。
create or replace procedure pro_monthSqlBEGIN #Routine body goes here... declare months int; declare i int default 0; declare sqlStr text; declare mtn varchar(20); set months = TIMESTAMPDIFF(MONTH,startTime,endTime); set sqlStr = 'select * from ('; repeat set mtn = DATE_FORMAT(DATE_ADD(startTime,INTERVAL i MONTH),'%Y-%m'); if i <= months THEN if i = 0 THEN set sqlStr = CONCAT(sqlStr, '(select ifnull(sum(total),0) orderTotal,', 'ifnull(sum(productSPrice * count),0) costTotal,', 'ifnull(DATE_FORMAT(orderTime,''%Y-%m''),\'',mtn,'\') `time`', 'from view_order_product_supplier where DATE_FORMAT(orderTime,''%Y-%m'')=','\'',mtn,'\')'); else set sqlStr = CONCAT(sqlStr,' union ', '(select ifnull(sum(total),0) orderTotal,', 'ifnull(sum(productSPrice * count),0) costTotal,', 'ifnull(DATE_FORMAT(orderTime,''%Y-%m''),\'',mtn,'\') `time`', 'from view_order_product_supplier where DATE_FORMAT(orderTime,''%Y-%m'')=','\'',mtn,'\')'); end if; end if; set i = i + 1; until i>months end repeat; set sqlStr = concat(sqlStr,')a group by a.`time` order by a.`time` asc'); -- select * from view_order_product_supplier; select sqlStr;END 执行这个存储过程后的SQL样式：
SELECT *FROM ( ( SELECT ifnull(sum(total), 0) orderTotal, ifnull(sum(productSPrice * count), 0) costTotal, ifnull( DATE_FORMAT(orderTime, '%Y-%m'), '2019-01' ) `time` FROM view_order_product_supplier WHERE DATE_FORMAT(orderTime, '%Y-%m') = '2019-01' ) UNION ( SELECT ifnull(sum(total), 0) orderTotal, ifnull(sum(productSPrice * count), 0) costTotal, ifnull( DATE_FORMAT(orderTime, '%Y-%m'), '2019-02' ) `time` FROM view_order_product_supplier WHERE DATE_FORMAT(orderTime, '%Y-%m') = '2019-02' ) UNION ( SELECT ifnull(sum(total), 0) orderTotal, ifnull(sum(productSPrice * count), 0) costTotal, ifnull( DATE_FORMAT(orderTime, '%Y-%m'), '2019-03' ) `time` FROM view_order_product_supplier WHERE DATE_FORMAT(orderTime, '%Y-%m') = '2019-03' ) UNION ( SELECT ifnull(sum(total), 0) orderTotal, ifnull(sum(productSPrice * count), 0) costTotal, ifnull( DATE_FORMAT(orderTime, '%Y-%m'), '2019-04' ) `time` FROM view_order_product_supplier WHERE DATE_FORMAT(orderTime, '%Y-%m') = '2019-04' ) UNION ( SELECT ifnull(sum(total), 0) orderTotal, ifnull(sum(productSPrice * count), 0) costTotal, ifnull( DATE_FORMAT(orderTime, '%Y-%m'), '2019-05' ) `time` FROM view_order_product_supplier WHERE DATE_FORMAT(orderTime, '%Y-%m') = '2019-05' ) UNION ( SELECT ifnull(sum(total), 0) orderTotal, ifnull(sum(productSPrice * count), 0) costTotal, ifnull( DATE_FORMAT(orderTime, '%Y-%m'), '2019-06' ) `time` FROM view_order_product_supplier WHERE DATE_FORMAT(orderTime, '%Y-%m') = '2019-06' ) UNION ( SELECT ifnull(sum(total), 0) orderTotal, ifnull(sum(productSPrice * count), 0) costTotal, ifnull( DATE_FORMAT(orderTime, '%Y-%m'), '2019-07' ) `time` FROM view_order_product_supplier WHERE DATE_FORMAT(orderTime, '%Y-%m') = '2019-07' ) UNION ( SELECT ifnull(sum(total), 0) orderTotal, ifnull(sum(productSPrice * count), 0) costTotal, ifnull( DATE_FORMAT(orderTime, '%Y-%m'), '2019-08' ) `time` FROM view_order_product_supplier WHERE DATE_FORMAT(orderTime, '%Y-%m') = '2019-08' ) UNION ( SELECT ifnull(sum(total), 0) orderTotal, ifnull(sum(productSPrice * count), 0) costTotal, ifnull( DATE_FORMAT(orderTime, '%Y-%m'), '2019-09' ) `time` FROM view_order_product_supplier WHERE DATE_FORMAT(orderTime, '%Y-%m') = '2019-09' ) ) aGROUP BY a.`time`ORDER BY a.`time` ASC
将上面的语句查询结果如下：
希望带来启发。