Mysql 生成按月份统计SQL语句，为null设置为0

为什么80%的码农都做不了架构师？>>>   

在管理系统开发过程中，经常会按照月份统计系统数据，并生成报表。常用的做法就是写代码生成月份一条条地在数据库中查询结果，并组装反馈到前台。

经过多次百度后，结合自己的理解，将生成这个的SQL语句写成存储过程，记录在此备忘。

create or replace procedure pro_monthSql
BEGIN
	#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'
			)
	) a
GROUP BY
	a.`time`
ORDER BY
	a.`time` ASC

将上面的语句查询结果如下：

希望带来启发。

转载于:https://my.oschina.net/capjes/blog/3051247