我有一张包含股票交易的表格：
+------+----------------------+------------------+
| 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项=’酒吧’
+------+-----------+------------+----------------------+------------------+
| 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’
+------+-----------+------------+----------------------+------------------+
| 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 |
+------+-----------+------------+----------------------+------------------+
所以像……
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)
可以这样做吗？
最佳答案
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列排序的结果感到满意,则显然可以省略外部查询.