取从MySQL表累计总和(Fetch cumulative sum from MySQL table

我有一个包含捐款表，我现在创建一个页面来查看统计数据。我想从总量和累计总数据库读取的月度数据。

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工作？任何想法，我怎么能解决这个问题？

谢谢，Lassi的

不变量子查询将做到这一点很容易，而且相当多的便携;

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`);

一个SQLfiddle测试用。