从MySQL表中获取累计和 [英] Fetch cumulative sum from MySQL table

问题描述

我有一张包含捐款的表格，现在我正在创建一个页面来查看统计信息。我想从数据库中获取每月数据的毛额和累计毛额。

  mysql>描述捐赠; 
 + ------------------ + ------------------ + ------ + ----- + --------- + ---------------- + 
 |字段|类型|空| Key |默认|额外| 
 + ------------------ + ------------------ + ------ + ----- + --------- + ---------------- + 
 | id | int（10）unsigned | NO | PRI | NULL | auto_increment | 
 | transaction_id | varchar（64）| NO | UNI | | | 
 | donor_email | varchar（255）| NO | | | | 
 | net |双| NO | | 0 | | 
 | gross |双| NO | | NULL | | 
 | original_request |文字| NO | | NULL | | 
 |时间| datetime | NO | | NULL | | 
 |声称| tinyint（4）| NO | | NULL | | 
 + ------------------ + ------------------ + ------ + ----- + --------- + ---------------- + 
  

以下是我试过的：

  SET @cgross = 0; 
 SELECT YEAR（`time`），MONTH（`time`），SUM（`gross`），（@cgross：= @cgross + SUM（`gross`））as`cumulative_gross` FROM`donations` GROUP按年（'时间'），月（'时间'）; 
  

结果是：

pre > + -------------- + --------------- + ----------- --- + ------------------ +
|年（'时间'）| MONTH（`time`）| SUM（`gross`）| cumulative_gross |
+ -------------- + --------------- + -------------- + ------------------ +
| 2013 | 1 | 257 | 257 |
| 2013 | 2 | 140 | 140 |
| 2013 | 3 | 311 | 311 |
| 2013 | 4 | 279 | 279 |
+ -------------- + --------------- + -------------- + ------------------ +


其中是错的。期望的结果是：

  + -------------- + ---- ----------- + -------------- + ------------------ + 
 |年（'时间'）| MONTH（`time`）| SUM（`gross`）| cumulative_gross | 
 + -------------- + --------------- + -------------- + ------------------ + 
 | 2013 | 1 | 257 | 257 | 
 | 2013 | 2 | 140 | 397 | 
 | 2013 | 3 | 311 | 708 | 
 | 2013 | 4 | 279 | 987 | 
 + -------------- + --------------- + -------------- + ------------------ + 
  

I没有SUM试过，并且按预期工作。

  SET @cgross = 0; 
 SELECT YEAR（`time`），MONTH（`time`），SUM（`gross`），（@cross：= @cross + 10）as`cumulative_gross` FROM`donations` GROUP BY 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（ `时间`），
 MONTH（`时间`），
 SUM（总），
（SELECT SUM（总）
捐赠
 WHERE`time`< ; = MAX（a.`time`））cumulative_gross 
捐款a GROUP BY YEAR（`time`），MONTH（`time`）; 
  

一个用测试的SQLfiddle。

I have a table containing donations, and I am now creating a page to view statistics. I would like to fetch monthly data from the database with gross and cumulative gross.

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    |                |
+------------------+------------------+------+-----+---------+----------------+

Here's what I've tried:

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

The result is:

+--------------+---------------+--------------+------------------+
| YEAR(`time`) | MONTH(`time`) | SUM(`gross`) | cumulative_gross |
+--------------+---------------+--------------+------------------+
|         2013 |             1 |          257 |              257 |
|         2013 |             2 |          140 |              140 |
|         2013 |             3 |          311 |              311 |
|         2013 |             4 |          279 |              279 |
+--------------+---------------+--------------+------------------+

Which is wrong. The desired result would be:

+--------------+---------------+--------------+------------------+
| YEAR(`time`) | MONTH(`time`) | SUM(`gross`) | cumulative_gross |
+--------------+---------------+--------------+------------------+
|         2013 |             1 |          257 |              257 |
|         2013 |             2 |          140 |              397 |
|         2013 |             3 |          311 |              708 |
|         2013 |             4 |          279 |              987 |
+--------------+---------------+--------------+------------------+

I tried this without SUM, and it did work as expected.

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 |
+--------------+---------------+--------------+------------------+

Why doesn't it work with SUM? Any ideas how I could fix it?

Thanks, Lassi

解决方案

A subquery without variables will do it just as easily, and quite a bit more portably;

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表中获取累计和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！