计算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语句来首先初始化运行的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.