SQL Server 2016 - 计算剩余库存数量 [英] SQL Server 2016 - Calculating remaining stock quantity
问题描述
我有一张这样的桌子:
如您所见,我有一列名为 left_stock 的列对于每个项目的第一行是正确的,但对于后续行则不正确.第一行很简单,因为您可以简单地从 initial_stock 中减去订单数量..
As you can see, I have a column called remaining_stock that is correct for the first line of each item but NOT for the subsequent lines. The first line is straight forward as you can simply subtract the order-quantity from initial_stock..
我想要实现的是获得如下所示的剩余库存列:
What I want to achieve is to get a remaining_stock column that looks like this:
我虽然使用 row_number 然后使用行号连接回同一个表......但这也不太奏效.有人能指出我正确的方向吗?
I though of using row_number and then joining back to the same table using the row numbers.. but that doesn't quite work either. can someone point me in the right direction please?
select 1 as line, 123 as item, 5 as order_quantity,10 as intial_stock
union all
select 2 as line, 123 as item, 3 as order_quantity,10 as intial_stock
union all
select 3 as line, 123 as item, 1 as order_quantity,10 as intial_stock
union all
select 4 as line, 234 as item, 5 as order_quantity,15 as intial_stock
union all
select 5 as line, 234 as item, 3 as order_quantity,15 as intial_stock
union all
select 6 as line, 234 as item, 1 as order_quantity,15 as intial_stock
推荐答案
使用窗口函数 Sum() over 的小事
Small matter using the window function Sum() over
示例
Select *
,Remaining_Stock = intial_stock - sum(Order_Quantity) over (Partition By Item Order by Line)
from YourTable
退货
line item order_quantity intial_stock Remaining_Stock
1 123 5 10 5
2 123 3 10 2
3 123 1 10 1
4 234 5 15 10
5 234 3 15 7
6 234 1 15 6
这篇关于SQL Server 2016 - 计算剩余库存数量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!