本文介绍了在 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 中计算运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！