在 MySQL 中计算运行总计

2022-01-30 00:00:00 sql 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.

相关文章