Mysql按月累计求和

思路：其实实现累计的核心就是将相同的表在做一次关联，让里面的值作为比较参数。
按月累加：
需求：结果表使用distinct过滤重复字段后count
表名：case_result
字段：gmt_created 创建时间

以下为统计的各个月份的count数据：

select DATE_FORMAT(gmt_create,'%Y%m') months, count(DISTINCT case_name, case_type, run_product_name) count from case_result WHERE case_type = #{caseType} group by months;

目标：针对于累计求和，在统计出各个月份的count后要是当前月份的count数等于前几个月count之和，我们在之前的基础上加上月份关联即可。

第一种方式：以on为连接条件

SELECT
	s1.months, s1.case_type,
	sum( s2.num ) as total 
FROM
	( SELECT DATE_FORMAT( gmt_create, '%Y-%m' ) months, count( DISTINCT case_name, case_type, run_product_name ) num, case_type
	FROM case_result WHERE case_type =#{caseType}
	GROUP BY 
	months ) s1
JOIN ( SELECT DATE_FORMAT( gmt_create, '%Y-%m' ) months, count( DISTINCT case_name, case_type, run_product_name ) num, case_type 
		FROM case_result WHERE case_type = #{caseType}
		GROUP BY 
		months ) s2 
ON   s1.months >= s2.months 
GROUP BY
	s1.months;

第二种方式：以where为连接条件

SELECT
	s1.months, s1.case_type,
	sum( s2.num ) as total 
FROM
	( SELECT DATE_FORMAT( gmt_create, '%Y-%m' ) months, count( DISTINCT case_name, case_type, run_product_name ) num, case_type
	FROM case_result WHERE case_type =#{caseType}
	GROUP BY 
	months ) s1
JOIN ( SELECT DATE_FORMAT( gmt_create, '%Y-%m' ) months, count( DISTINCT case_name, case_type, run_product_name ) num, case_type 
		FROM case_result WHERE case_type = #{caseType}
		GROUP BY 
		months ) s2 
ON  1=1 
WHERE  s1.months >= s2.months 
GROUP BY
	s1.months;

实现后的效果：