显示对"total“列的更改的SQL查询
显示对"total“列的更改的SQL查询
提问于 2012-05-21 18:41:23
我有一个包含股票交易的表：
+------+----------------------+------------------+
| 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='bar'
+------+-----------+------------+----------------------+------------------+
| 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 Item='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)这可以做到吗？
回答 5
Stack Overflow用户
回答已采纳
发布于 2012-05-21 19:19:21
SELECT `Item`,
`Stock Up`,
`Stock Down`,
`Running Stock Total`,
`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`,
`Running Stock Total`,
`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列，那么显然可以省略外部查询。
Stack Overflow用户
发布于 2012-05-21 19:40:38
我的解决方案基于这样一个假设:每个Item的Transaction Time都是唯一的。
我正在通过我创建的助手视图模拟row_number()分析函数：
CREATE VIEW running_stock AS
SELECT s.item,s.running_total,s.transaction_dt,
(SELECT count(*) FROM stock WHERE item=s.item
AND transaction_dt <= s.transaction_dt) AS row_number
FROM stock s
ORDER BY 1, 4;视图就位后，您可以通过以下查询获得想要的结果：
SELECT c.item AS "Item",
greatest(c.running_total - p.running_total, 0) AS "Stock Up",
greatest(p.running_total - c.running_total, 0) AS "Stock Down",
c.running_total AS "Running Total",
c.transaction_dt AS "Transaction Time"
FROM running_stock c
LEFT JOIN running_stock p ON c.item = p.item
AND p.row_number + 1 = c.row_number
WHERE c.row_number > 1
ORDER BY 1, 5;您可以在SQL Fiddle上使用此查询。
Stack Overflow用户
发布于 2012-05-21 18:58:42
一种解决方案可能是：
- 创建游标以一次选择每一行
检查值是否更改，如果更改，则在#temp表中插入信息
- 从#temp table
中选择所有数据
如果可以按顺序标识所有行，则可以使用while循环来代替游标。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/10683748
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号