MySQL查询计算部分和 [英] MySQL query that computes partial sums

问题描述

我应该在MySQL数据库中执行哪个查询以获得包含源表的部分和的结果?

What query should I execute in MySQL database to get a result containing partial sums of source table?

例如，当我有桌子时:

Id|Val
1 | 1
2 | 2
3 | 3
4 | 4

我想要这样的结果:

Id|Val
1 | 1
2 | 3 # 1+2
3 | 6 # 1+2+3
4 | 10 # 1+2+3+4

现在，我得到了一个包含游标和while循环的存储过程的结果.我想找到一种更好的方法.

Right now I get this result with a stored procedure containing a cursor and while loops. I'd like to find a better way to do this.

推荐答案

您可以通过将表自身联接来实现此目的. SUM将累加到该行的所有行:

You can do this by joining the table on itself. The SUM will add up all rows up to this row:

select cur.id, sum(prev.val)
from TheTable cur
left join TheTable prev
    on cur.id >= prev.id
group by cur.id

MySQL还允许使用用户变量来计算此值，这虽然效率更高，但被认为是一种hack:

MySQL also allows the use of user variables to calculate this, which is more efficient but considered something of a hack:

select 
     id
,    @running_total := @running_total + val AS RunningTotal
from TheTable

这篇关于MySQL查询计算部分和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！