本文介绍了计算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中的运行总计
发布评论