blocks|key|1600725|text|我要说的是，这是不可能的，每个结果行都应该是独立的。使用编程语言获取这些值|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1600726|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I would say that this is impossible every resulting row should be independent. Use programming language for getting these values

blocks|key|1600748|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1600749|这将需要一些语法调优(我没有使用MySQL对其进行测试)，但它向您展示了想法。THe子查询只需返回并添加您已包含在外部查询中的所有新内容，并且它必须对每一行执行此操作。|unstyled|1600750|看看this+question，了解如何使用joins来实现同样的目的。|offset|length|1600751|为了解决数据增长带来的性能下降问题:因为存在最大。一年中有366天，并且我假设您不是针对多年运行此查询，则该子查询将被计算多达366次。有了适当的日期索引和hasPaid标志，您就可以了。|1600752|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/439138/running-total-by-grouped-records-in-table^0|0|0|2|D|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|V|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|W|8|@]|9|@$I|X|J|Y|1|Z]]|A|$]]|$1|K|3|L|5|F|7|10|8|@]|9|@]|A|$]]|$1|M|3|-4|5|F|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

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

This will require some syntactical tuning (I don't have MySQL to test it), but it shows you the idea. THe subquery just has to go back and add up everything fresh that you already included in the outer query, and it has to do that for every row. 

Take a look at <a href="https://stackoverflow.com/questions/439138/running-total-by-grouped-records-in-table">this question</a> for how to use joins to accomplish the same.

To address concerns about performance degradation with growing data: Since there are max. 366 days in a year, and I assume that you are not running this query against multiple years, the subquery will get evaluated up to 366 times. With proper indices on the date and the hasPaid flag, you'll be ok.

blocks|key|1385940|text|也许对您来说这是一个更简单的解决方案，并且可以避免数据库执行大量的查询。它只执行一次查询，然后在一次遍历中对结果进行一些数学运算。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1385941|SET+@runtot:=0;
SELECT
+++q1.d,
+++q1.c,
+++(@runtot+:=+@runtot+%2B+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|code-block|syntax|javascript|1385942|这将为您提供一个额外的RT+(运行总数)列。不要错过顶部的SET语句，它首先初始化正在运行的total变量，否则只会得到一列空值。|1385943|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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.

<pre><code>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` &gt; 0
 GROUP BY d
 ORDER BY d) AS q1
</code></pre>

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.

blocks|key|1385969|text|从MySQL+8开始，您将使用window+functions进行这种查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1385970|SELECT+dayofyear(`date`)+AS+d,+count(*),+sum(count(*))+OVER+(ORDER+BY+dayofyear(`date`))
FROM+`orders`
WHERE+`hasPaid`+>+0
GROUP+BY+d
ORDER+BY+d|code-block|syntax|javascript|1385971|在上面的查询中，聚合函数count(*)嵌套在窗口函数sum(..)+OVER+(..)中，由于logical+order+of+operations+in+SQL，这是可能的。如果这太令人困惑，您可以很容易地求助于使用派生表或WITH+clause来更好地构造查询：|style|CODE|1385972|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|1385973|entityMap|0|LINK|mutability|MUTABLE|url|https://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/|1|https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/|2|https://dev.mysql.com/doc/refman/8.0/en/with.html^0|F|G|0|0|0|C|8|R|H|36|4|1C|Y|1|36|B|2|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|F|7|14|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|15|8|@$A|16|B|17|K|L]|$A|18|B|19|K|L]|$A|1A|B|1B|K|L]]|9|@$A|1C|B|1D|1|1E]|$A|1F|B|1G|1|1H]]|C|$]]|$1|M|3|N|5|F|7|1I|8|@]|9|@]|C|$G|H]]|$1|O|3|-4|5|6|7|1J|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]|W|$5|R|S|T|C|$U|X]]|Y|$5|R|S|T|C|$U|Z]]]]

Starting with MySQL 8, you will be using <a href="https://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/" rel="noreferrer">window functions</a> for this kind of query:

<pre class="lang-sql prettyprint-override"><code>SELECT dayofyear(`date`) AS d, count(*), sum(count(*)) OVER (ORDER BY dayofyear(`date`))
FROM `orders`
WHERE `hasPaid` &gt; 0
GROUP BY d
ORDER BY d
</code></pre>

In the above query, the aggregate function <code>count(*)</code> is nested inside of the window function <code>sum(..) OVER (..)</code>, which is possible because of the <a href="https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/" rel="noreferrer">logical order of operations in SQL</a>. If that's too confusing, you can easily resort to using a derived table or a <a href="https://dev.mysql.com/doc/refman/8.0/en/with.html" rel="noreferrer"><code>WITH</code> clause</a> to better structure your query:

<pre class="lang-sql prettyprint-override"><code>WITH daily (d, c) AS (
 SELECT dayofyear(`date`) AS d, count(*)
 FROM `orders`
 WHERE `hasPaid` &gt; 0
 GROUP BY d
)
SELECT d, c, sum(c) OVER (ORDER BY d)
ORDER BY d
</code></pre>

blocks|key|1600868|text|可以使用MySQL中的临时表计算运行余额。下面的查询应该可以工作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1600869|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;|code-block|syntax|javascript|1600870|临时表用于组织查询。请注意，临时表仅在与MySQL服务器的连接期间存在|1600871|上面的查询使用子查询，它返回临时表中所有行(包括当前行)的余额。余额被分配给实际表中的当前行|1600872|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

It is possible to calculate a running balance using a temporary table in MySQL. The following query should work:

<pre><code>CREATE TEMPORARY table orders_temp1 (SELECT id, DAYOFYEAR(`date`) AS d, COUNT(*) as total FROM `orders` WHERE `hasPaid` &gt; 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&lt;=t1.id) as running_total FROM orders_temp1 t1;
</code></pre>

A temporary table is used for organizing the query. Note that a temporary table only exists for the duration of the connection to the MySQL server

The above query uses a sub query, which returns balance of all rows in the temporary table upto and including the current row. The balance is assigned to the current row in the actual table

I have this MySQL query:

<pre><code>SELECT DAYOFYEAR(`date`) AS d, COUNT(*) 
FROM `orders` 
WHERE `hasPaid` &gt; 0
GROUP BY d
ORDER BY d
</code></pre>

Which returns something like this:

<pre><code>d | COUNT(*) |
20 | 5 |
21 | 7 |
22 | 12 |
23 | 4 |
</code></pre>

What I'd really like is another column on the end to show the running total:

<pre><code>d | COUNT(*) | ??? |
20 | 5 | 5 |
21 | 7 | 12 |
22 | 12 | 24 |
23 | 4 | 28 |
</code></pre>

Is this possible?

Calculate a running total in MySQL

数据库

服务器

MySQL

我有这个MySQL查询：SELECT DAYOFYEAR(`date`)  AS d, COUNT(*) FROM  `orders` WHERE  `hasPaid` > 0GROUP  BY dORDER  BY d它返回的内容如下所示：d  | COUNT(*) |20 |  5       |21 |  7  ...

问在MySQL中计算运行合计
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中计算运行合计EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中计算运行合计
EN