mysql - 在mysql中添加行

我在其中有一个名为temp_reading的表，下面的列是，消耗是主键：

id consumption total
1  100
1  200
1  300
1  400
2  50
2  100
3  200
4  250

现在我想显示总计为

id   consumption  total
1    100          100
1    200          300
1    300          600
1    300          900
2    50           50
2    100          150
3    200          200
4    250          250

是否可以像上面一样显示
我尝试了以下查询

select id,consumption,sum(consumption) as total
from temp_reading
group by consumption;

请帮我解决这个问题

最佳答案

SELECT  id,
        consumption,
         @accum:=@accum + a.runningTotal AS TOTAL
FROM
    (
      SELECT  id,
              consumption,
              SUM(consumption) AS runningTotal
      FROM table1
      GROUP BY id, consumption
      ORDER BY id, consumption
    ) a , (SELECT @accum := 0) s;

SQLFiddle Demo