在MySQL中创建累积总和列 [英] Create a Cumulative Sum Column in MySQL

问题描述

我有一个看起来像这样的表:

I have a table that looks like this:

id   count
1    100
2    50
3    10

我想添加一个新列，称为cumulative_sum，因此该表将如下所示:

I want to add a new column called cumulative_sum, so the table would look like this:

id   count  cumulative_sum
1    100    100
2    50     150
3    10     160

是否存在可以轻松实现此目的的MySQL更新语句?最好的方法是什么?

Is there a MySQL update statement that can do this easily? What's the best way to accomplish this?

推荐答案

如果性能存在问题，则可以使用MySQL变量:

If performance is an issue, you could use a MySQL variable:

set @csum := 0;
update YourTable
set cumulative_sum = (@csum := @csum + count)
order by id;

或者，您可以删除cumulative_sum列并在每个查询中对其进行计算:

Alternatively, you could remove the cumulative_sum column and calculate it on each query:

set @csum := 0;
select id, count, (@csum := @csum + count) as cumulative_sum
from YourTable
order by id;

这将以运行方式计算运行总和:)

This calculates the running sum in a running way :)

这篇关于在MySQL中创建累积总和列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！