我有一张包含股票交易的表格:
@H_502_6@+------+----------------------+------------------+ | Item | Running Stock Total | Transaction Time | +------+----------------------+------------------+ | foo | 4 | 2012-05-12 11:07 | | bar | 3 | 2012-05-12 10:42 | | bar | 3 | 2012-05-12 9:42 | | bar | 2 | 2012-05-11 15:42 | | foo | 3 | 2012-05-11 10:02 | | bar | 3 | 2012-05-10 13:44 | ...etc... +------+----------------------+------------------+
即,任何时候发生库存事件,在此表中创建一行 – 这可能意味着库存水平上升(订购新库存),下降(库存销售)或保持不变(库存重新定位).
我需要创建一个SQL查询,该查询仅返回特定部分的库存级别实际更改的行,并且需要在“库存增加”和“库存减少”列中显示更改.
即1项=’酒吧’
@H_502_6@+------+-----------+------------+----------------------+------------------+ | Item | Stock Up | Stock Down | Running Stock Total | Transaction Time | +------+-----------+------------+----------------------+------------------+ | bar | 1 | 0 | 3 | 2012-05-12 9:42 | | bar | 0 | 1 | 2 | 2012-05-11 15:42 | | bar | 1 | 0 | 3 | 2012-05-10 13:44 | +------+-----------+------------+----------------------+------------------+
例如2项=’foo’
@H_502_6@+------+-----------+------------+----------------------+------------------+ | Item | Stock Up | Stock Down | Running Stock Total | Transaction Time | +------+-----------+------------+----------------------+------------------+ | foo | 1 | 0 | 4 | 2012-05-12 11:07 | | foo | 2 | 0 | 3 | 2012-05-11 10:02 | +------+-----------+------------+----------------------+------------------+
所以像……
@H_502_6@SELECT Item,{xyz} as 'Stock Up',{abc} as 'Stock Down',`Running Stock Total`,`Transaction Time` FROM `StockTransactions` WHERE `Item`='foo' HAVING ('Stock Up'>0 or 'Stock Down'>0)
可以这样做吗?
最佳答案
@H_502_6@SELECT `Item`,`Stock Up`,`Stock Down`,`Transaction Time` FROM ( SELECT `Item`,GREATEST(`Running Stock Total` - @`last_total`,0) AS `Stock Up`,GREATEST(@`last_total` - `Running Stock Total`,0) AS `Stock Down`,`Transaction Time`,@`last_total` := `Running Stock Total` FROM `StockTransactions` JOIN (SELECT @`last_total` := 0) AS lt WHERE `Item` = 'bar' ORDER BY `Transaction Time` ASC ) AS t ORDER BY `Transaction Time` DESC
在sqlfiddle上查看.如果您对按交易时间的升序和额外的last_total列排序的结果感到满意,则显然可以省略外部查询.