在 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 语句来首先初始化运行总计变量，否则您只会得到一列 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屋！