我有一个包含捐赠的表，现在我正在创建一个页面来查看统计数据。我想从数据库中获取每月的毛额和累计毛额数据。
mysql> describe donations;
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| transaction_id | varchar(64) | NO | UNI | | |
| donor_email | varchar(255) | NO | | | |
| net | double | NO | | 0 | |
| gross | double | NO | | NULL | |
| original_request | text | NO | | NULL | |
| time | datetime | NO | | NULL | |
| claimed | tinyint(4) | NO | | NULL | |
+------------------+------------------+------+-----+---------+----------------+
我试过的是：
SET @cgross = 0;
SELECT YEAR(`time`), MONTH(`time`), SUM(`gross`), (@cgross := @cgross + SUM(`gross`)) AS `cumulative_gross` FROM `donations` GROUP BY YEAR(`time`), MONTH(`time`);
结果是：
+--------------+---------------+--------------+------------------+
| YEAR(`time`) | MONTH(`time`) | SUM(`gross`) | cumulative_gross |
+--------------+---------------+--------------+------------------+
| 2013 | 1 | 257 | 257 |
| 2013 | 2 | 140 | 140 |
| 2013 | 3 | 311 | 311 |
| 2013 | 4 | 279 | 279 |
+--------------+---------------+--------------+------------------+
这是错误的。期望的结果是：
+--------------+---------------+--------------+------------------+
| YEAR(`time`) | MONTH(`time`) | SUM(`gross`) | cumulative_gross |
+--------------+---------------+--------------+------------------+
| 2013 | 1 | 257 | 257 |
| 2013 | 2 | 140 | 397 |
| 2013 | 3 | 311 | 708 |
| 2013 | 4 | 279 | 987 |
+--------------+---------------+--------------+------------------+
我试过这个，但没有用，果然成功了。
SET @cgross = 0;
SELECT YEAR(`time`), MONTH(`time`), SUM(`gross`), (@cgross := @cgross + 10) AS `cumulative_gross` FROM `donations` GROUP BY YEAR(`time`), MONTH(`time`);
+--------------+---------------+--------------+------------------+
| YEAR(`time`) | MONTH(`time`) | SUM(`gross`) | cumulative_gross |
+--------------+---------------+--------------+------------------+
| 2013 | 1 | 257 | 10 |
| 2013 | 2 | 140 | 20 |
| 2013 | 3 | 311 | 30 |
| 2013 | 4 | 279 | 40 |
+--------------+---------------+--------------+------------------+
为什么它不能和SUM一起工作？你知道我该怎么解决吗？
谢谢，
拉西
最佳答案
一个不带变量的子查询也可以很容易地完成，而且更便于移植；
SELECT YEAR(`time`),
MONTH(`time`),
SUM(gross),
(SELECT SUM(gross)
FROM donations
WHERE `time`<=MAX(a.`time`)) cumulative_gross
FROM donations a GROUP BY YEAR(`time`), MONTH(`time`);
An SQLfiddle to test with。
关于mysql - 从MySQL表中获取累积总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16322133/