计算MySQL中的运行总计 [英] Calculate a running total in MySQL

问题描述

我有这个MySQL查询:

I have this MySQL query:

SELECT DAYOFYEAR(`date`)  AS d, COUNT(*) 
FROM  `orders` 
WHERE  `hasPaid` > 0
GROUP  BY d
ORDER  BY d

哪个返回如下内容:

d  | COUNT(*) |
20 |  5       |
21 |  7       |
22 | 12       |
23 |  4       |

我真正想要的是末尾的另一列以显示运行总计:

What I'd really like is another column on the end to show the running total:

d  | COUNT(*) | ??? |
20 |  5       |   5 |
21 |  7       |  12 |
22 | 12       |  24 |
23 |  4       |  28 |

这可能吗?

推荐答案

也许为您提供了一个更简单的解决方案，并且避免了数据库必须执行大量查询.这样只会执行一个查询，然后只需一次就可以对结果进行一些数学运算.

Perhaps a simpler solution for you and prevents the database having to do a ton of queries. This executes just one query then does a little math on the results in a single pass.

SET @runtot:=0;
SELECT
   q1.d,
   q1.c,
   (@runtot := @runtot + q1.c) AS rt
FROM
   (SELECT
       DAYOFYEAR(`date`) AS d,
       COUNT(*) AS c
    FROM  `orders`
    WHERE  `hasPaid` > 0
    GROUP  BY d
    ORDER  BY d) AS q1

这将为您提供一个附加的RT(运行总计)列.不要错过顶部的SET语句来首先初始化运行的total变量，否则您将只获得一列NULL值.

This will give you an additional RT (running total) column. Don't miss the SET statement at the top to initialize the running total variable first or you will just get a column of NULL values.

这篇关于计算MySQL中的运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！