在 MySQL 中计算运行总计

Calculate a running total in MySQL(在 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 中计算运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！