在MySQL中计算运行总计

我有这个MySQL查询：

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       |

真的很喜欢在结尾的另一列显示运行总额：

在MySQL中创建累积总和列的可能重复项

@Ztyx一年多以后，您的链接问题被问到。因此，恰恰相反。

#1 楼

也许对您来说是一个更简单的解决方案，并且可以防止数据库不得不执行大量查询。这将只执行一个查询，然后通过一次对结果进行一点数学运算。不要错过顶部的SET语句来首先初始化运行的total变量，否则您将只获得一列NULL值。

效果很棒！通过查看EXPLAIN可以看出，它比以前接受的答案要有效得多。

–nickf
09年8月17日在23:04

关键是要使用子查询。这使得它在涉及多个表或聚合的复杂查询中非常可靠。

–ÁlvaroGonzález
2011年8月2日，11：41

对于那些想使用PHP的基本MySQL函数执行类似操作的人，请确保单独运行第一行（但仍要在第二行之前）。

–user778005
2011年8月11日12:50

Ariel，请提供一个工作示例作为该帖子的答案，而不是一意孤行。很想看看您的建议如何运作！

–安德鲁·树篱
2013年1月3日15:47

值得一提的是，可以在FROM子句的SELECT语句中像这样设置@rentot变量：... AS q1，（SELECT @runtot：= 0）AS n。这可能会使php人员的生活变得更加轻松，因为现在您只有一个语句查询。

–足月
13年1月27日在8:17

#2 楼

SELECT 
   DAYOFYEAR(O.`date`)  AS d, 
   COUNT(*),
   (select count(*) from `orders` 
       where  DAYOFYEAR(`date`) <= d and   `hasPaid` > 0)
FROM  
  `orders` as O
WHERE  
  O.`hasPaid` > 0
GROUP  BY d
ORDER  BY d

这将需要一些语法上的调整（我没有MySQL对其进行测试），但是它向您展示了这个想法。子查询只需要返回并添加外部查询中已经包含的所有新鲜内容，并且它必须为每一行执行该操作。

看看这个问题，了解如何使用联接来实现相同的目的。一年中有366天，并且我假设您没有跨多年运行此查询，则该子查询将获得366次评估。在日期和hasPaid标志上使用适当的索引，您就可以了。

请注意，这在大型数据库，平均数据库和某些小型数据库上将非常慢，因为它需要执行尽可能多的附加查询，因为结果中将有行

–谢尔盖·安德列耶夫（Sergej Andrejev）
09年3月20日在2:18

同意。我为此答案+1，因为它很聪明，并且在需要时我们都使用过这样的解决方案，但我们也都知道这是有代价的。取决于您需要运行计数的位置。对于业务逻辑？然后，也许在数据库中执行此操作。对于视图？用代码做。

–贾里·哈迪（Jarret Hardie）
09年3月20日在2:24

#3 楼

从MySQL 8开始，您将使用窗口函数进行此类查询：

 SELECT dayofyear(`date`) AS d, count(*), sum(count(*)) OVER (ORDER BY dayofyear(`date`))
FROM `orders`
WHERE `hasPaid` > 0
GROUP BY d
ORDER BY d

在上面的查询中，聚合函数count(*)嵌套在窗口函数sum(..) OVER (..)内，这是可能的，因为SQL中操作的逻辑顺序。如果这太令人困惑，则可以轻松地使用派生表或WITH子句来更好地构建查询结构：

 WITH daily (d, c) AS (
  SELECT dayofyear(`date`) AS d, count(*)
  FROM `orders`
  WHERE `hasPaid` > 0
  GROUP BY d
)
SELECT d, c, sum(c) OVER (ORDER BY d)
ORDER BY d

#4 楼

我要说，这不可能每个结果行都应该独立。使用编程语言获取这些值

考虑到关系数学的性质，以及您正在使用group by的事实，即使mysql有一定的技巧可以做到这一点，按照Sergej的建议，仅使用编程语言进行操作也不会太麻烦。

–贾里·哈迪（Jarret Hardie）
09年3月20日在2:05

我不同意。从重用和维护的角度来看，在数据库和应用程序层之间分配处理任务是有问题的。如果要在不同的地方使用此数据，例如在报表和屏幕上，则必须重复运行总计逻辑。

– cdonner
09年3月20日在2:08

+1您是对的：这在编程逻辑上会更容易且整体上更好-我正在尝试查看是否有一些神奇的很棒的功能可以做到。

–nickf
09年3月20日在2:14

正在运行的总计列不是视图逻辑。它是表中嵌入的数据。是的，SQL是关系集逻辑，但是几乎总是有序的。暗示这是不可能的，这是不正确的。提示其格式不佳需要讨论。

–布伦丹
13年10月10日，下午3:48

对于现代DBMS（使用窗口函数），这实际上非常容易，远非“不可能”

– a_horse_with_no_name
2014年5月20日在22:35

#5 楼

除非您别无选择，否则只能在sql中执行此操作，否则我将以进行查询的编程语言对结果求和。随着表格的增长，这样的嵌套会变得非常缓慢。

性能将随着表的大小而增长，但不会随着表的大小而激增，因为会计算并保留该值。依赖于子选择的其他方法将更加昂贵。

–布伦丹
13年10月10日，下午3:50

#6 楼

您可以使用Cross Join语句或一些slef联接来破解它，但是对于任何大型数据集，它都会变慢，因此最好在后查询处理器中完成；客户端代码中的任一光标

#7 楼

这是仅有的几个游标比基于集合的查询快的地方，如果性能至关重要，我可以在MySql
或
之外执行此操作使用MySql 5游标

#8 楼

可以使用MySQL中的临时表来计算运行余额。以下查询应该起作用：

CREATE TEMPORARY table orders_temp1 (SELECT id, DAYOFYEAR(`date`)  AS d, COUNT(*) as total FROM  `orders` WHERE  `hasPaid` > 0 GROUP BY d ORDER  BY d);
CREATE TEMPORARY table orders_temp2 (SELECT * FROM orders_temp1);
SELECT d, total, (SELECT SUM(t2.total) FROM orders_temp2 t2 WHERE t2.id<=t1.id) as running_total FROM orders_temp1 t1;

临时表用于组织查询。请注意，临时表仅在连接到MySQL服务器的持续时间内存在。

以上查询使用子查询，该子查询返回临时表中直到当前行的所有行的余额。余额将分配给实际表中的当前行

该查询使用存储在内存中的临时表。它们比基于磁盘的表快得多。但是，如果您的表中有大量数据，或者查询将同时由多个用户执行，则使用临时表会使查询变慢。请参阅：stackoverflow.com/questions/18865438/…

– Nadir Latif
3月2日13:26

编程黑洞网