计数每个ID [mySQL]的累积/增加

Question

我想从下面的表（table_values）计数每个记录的增加值：

  id | open | closed | in_progress | project |
  1  | 0    | 0      | 0           | 20      | 
  2  | 1    | 0      | 1           | 20      |
  3  | 1    | 1      | 1           | 55      | 
  4  | 1    | 1      | 1           | 20      | 
  5  | 1    | 1      | 1           | 20      | 
  6  | 2    | 2      | 0           | 20      | 

因此，例如选择项目= 20结果应为：

 id | open | closed | in_progress | project | Total |
 1  | 0    | 0      | 0           | 20      |  0    |
 2  | 1    | 0      | 1           | 20      |  2    |
 4  | 2    | 1      | 2           | 20      |  3    |
 5  | 3    | 2      | 3           | 20      |  3    |
 6  | 5    | 4      | 3           | 20      |  4    |

选择如果可能的话，应为每个ID返回累积结果。有什么建议吗？

问候。

更新：表：

  id | open | 
  1  | 2    |
  2  | 3    |
  3  | 5    | 

结果：

  id | open | cumulative_open
  1  | 2    | 2
  2  | 3    | 5
  3  | 5    | 10

Answer 1

您可以与同一项目的所有先前（包括相同的）行一起加入行，并使用SUM()：

select t1.id,
    sum(t2.open) as open,
    sum(t2.closed) as closed,
    sum(t2.in_progress) as in_progress,
    t1.project,
    t1.open + t1.closed + t1.in_progress as Total
from table_values t1
join table_values t2
  on  t2.project = t1.project
  and t2.id <= t1.id
where t1.project = 20
group by t1.id

演示：http：//rextester.com/nzdn42998

这是一个昂贵的查询（就性能而言） - 但至少是可靠的。

Answer 2

此方法使用参数并看起来可以实现您所描述的内容。不过，您可能需要修改名称，因为有些可能有点不合适（因为它们不适合保留名称）

SET @open = 0;
SET @closed = 0;
SET @in_progress = 0;
select 
id,
(@open := @open + open) as open,
(@closed := @closed + closed) as closed,
(@in_progress := @in_progress + in_progress) as in_progress,
project,
(open + closed + in_progress) as Total
FROM table_values
where project = 20
group by id;