MySQL查询累计值的方法

假设有借贷记录表，字段有借入，贷出，以及记账日期。表中记录如下所示：

 

现在想统计某一天，或者某一时间段内，每天的借贷累总额，以及历史累计总额，比如，2021-10-07日的借贷总额是1100，1000，历史累计值是6500，5500。那在MySQL中如何统计呢？方法有多种。下面演示某段时间内，每天的借贷总额以及历史累计总额的求法。如果求某一天的情况，则where条件就更简单了。

1. 先利用函数sum()求出每天的借贷总额，再利用窗口函数over()求历史累计值（窗口函数对MySQL版本有要求）。

select tmp.book_date, tmp.day_debit, tmp.accu_debit, tmp.day_credit, tmp.accu_credit
from(
	select t.book_date, t.day_debit, sum(t.day_debit) over(order by book_date)  as accu_debit, t.day_credit, sum(t.day_credit) over(order by book_date)  as accu_credit
	from  ( 
			SELECT book_date, sum(debit) as day_debit, sum(credit) as day_credit
			FROM test 
			GROUP BY book_date
			ORDER BY book_date
	) t
	group by t.book_date 
) tmp
where tmp.book_date >= '2021-10-05' and tmp.book_date <= '2021-10-07'

 2. 先利用函数sum()求出每天的借贷总额，再利用临时变量求历史累计值。

select tmp.book_date, tmp.day_debit, tmp.accu_debit, tmp.day_credit, tmp.accu_credit
from(
	select t.book_date, t.day_debit, @accu_debit:= @accu_debit + t.day_debit as accu_debit, t.day_credit, @accu_credit:= @accu_credit + t.day_credit as accu_credit
	from  ( 
			SELECT book_date, sum(debit) as day_debit, sum(credit) as day_credit
			FROM test 
			GROUP BY book_date
			ORDER BY book_date
	) t, (select @accu_debit:=0, @accu_credit:=0) c
	group by t.book_date 
) tmp
where tmp.book_date >= '2021-10-05' and tmp.book_date <= '2021-10-07'

3. 先利用函数sum()求出每天的借贷总额，再利用join求出历史累计值。

SELECT a.book_date, a.day_debit, SUM(b.day_debit) AS accu_debit, a.day_credit, SUM(b.day_credit) AS accu_credit
FROM (
		SELECT book_date, sum(debit) as day_debit, sum(credit) as day_credit
		FROM test 
		GROUP BY book_date
		ORDER BY book_date
) a
JOIN (
		SELECT book_date, sum(debit) as day_debit, sum(credit) as day_credit
		FROM test 
		GROUP BY book_date
		ORDER BY book_date
) b ON a.book_date >= b.book_date 
WHERE a.book_date >= '2021-10-05' and a.book_date <= '2021-10-07'
GROUP BY a.book_date
ORDER BY a.book_date;