MySQL动态加减行

Question

价目表：

date       | price
------------------
2014-01-01   100
2014-01-02   20
2014-01-03   -30
2014-01-04   70
...

是否可以使用SQL查询显示结果表？

成果表：

date       | price  | total
--------------------------
2014-01-01   100     100
2014-01-02   20      120
2014-01-03   -30     90
2014-01-04   70      160
...

1. “结果表”不是一个exist表，它只是sql结果。
2. “总计”一栏应用加法和减法显示客户余额的最后状态

是否可以编写此SQL查询？

Answer 1

select date, 
       price,
       @total := @total + price as total
from (select date, price
      from your_table
      order by date) x
CROSS JOIN (select @total := 0) r

Answer 2

select date, 
       @total := @total + price as running_total, 
       price
from your_table, (select @total := 0) r
order by date

Answer 3

使用相当标准的SQL；

SELECT *, (SELECT SUM(price) 
           FROM pricetable p 
           WHERE p.date<=pricetable.date) total
FROM pricetable
ORDER BY date;

一个要测试的SQLfiddle。

...or向左转.

SELECT *, SUM(p2.price) total
FROM pricetable p1
LEFT JOIN pricetable p2
  ON p1.date >= p2.date
GROUP BY p1.date, p1.price
ORDER BY p1.date;

另一个SQLfiddle。