MySQL实现累计求和

思路：其实实现累计的核心就是将相同的表在做一次关联，让里面的值作为比较参数。

一、按天累加

需求1：合同表按天汇总ROW_ID

数据库：mysql 5.7
表名：lnk_agreement
字段：created 创建时间；row_id 行ID

SELECT
	DATE_FORMAT( created, '%Y-%m-%d' ) AS day1,
	count( row_id ) AS total_day 
FROM
	lnk_agreement 
GROUP BY
	day1

需求2.合同表按天累计汇总ROW_ID

-- 方式一 on连接条件
SELECT
	t1.days,
	sum( t2.num ) as total 
FROM
	( SELECT DATE_FORMAT( created, '%Y-%m-%d' ) days, count( row_id ) num 
	FROM lnk_agreement 
	GROUP BY 
	DATE_FORMAT( created, '%Y-%m-%d' ) ) t1
JOIN ( SELECT DATE_FORMAT( created, '%Y-%m-%d' ) days, count( row_id ) num 
		FROM lnk_agreement 
		GROUP BY 
		DATE_FORMAT( created, '%Y-%m-%d' ) ) t2 
ON t1.days >= t2.days 
GROUP BY
	t1.days

--方式二 where 条件
SELECT
	t1.days,
	sum( t2.num ) as total 
FROM
	( SELECT DATE_FORMAT( created, '%Y-%m-%d' ) days, count( row_id ) num 
	FROM lnk_agreement 
	GROUP BY 
	DATE_FORMAT( created, '%Y-%m-%d' ) ) t1
JOIN ( SELECT DATE_FORMAT( created, '%Y-%m-%d' ) days, count( row_id ) num 
		FROM lnk_agreement 
		GROUP BY 
		DATE_FORMAT( created, '%Y-%m-%d' ) ) t2 
ON  1=1 
WHERE  t1.days >= t2.days 
GROUP BY
	t1.days

二、按月累加

需求1：合同表按月汇总ROW_ID

SELECT
	DATE_FORMAT( created, '%Y-%m' ) AS MONTH,
	count( row_id ) AS total 
FROM
	lnk_agreement 
GROUP BY
MONTH

需求2.合同表按月累计汇总ROW_ID

-- 方式一：on连接条件
SELECT
	t1.months,
	sum( t2.num ) AS total 
FROM
	( SELECT DATE_FORMAT( created, '%Y-%m' ) months, count( row_id ) num 
	FROM lnk_agreement 
	GROUP BY 
	DATE_FORMAT( created, '%Y-%m' ) ) t1
JOIN ( SELECT DATE_FORMAT( created, '%Y-%m' ) months, count( row_id ) num 
		FROM lnk_agreement 
		GROUP BY DATE_FORMAT( created, '%Y-%m-%d' ) ) t2 
ON t1.months >= t2.months 
	GROUP BY
	months

--方式二：where 条件
SELECT
	t1.months,
	sum( t2.num ) AS total 
FROM
	( SELECT DATE_FORMAT( created, '%Y-%m' ) months, count( row_id ) num 
	FROM lnk_agreement 
	GROUP BY 
	DATE_FORMAT( created, '%Y-%m' ) ) t1
JOIN ( SELECT DATE_FORMAT( created, '%Y-%m' ) months, count( row_id ) num 
		FROM lnk_agreement 
		GROUP BY DATE_FORMAT( created, '%Y-%m-%d' ) ) t2 
ON 1=1
WHERE t1.months >= t2.months 
	GROUP BY
	months