没有游标的TSQL帮助

Question

我有以下数据，我需要的输出是在列:新库存． 当前使用游标来派生值，但存在性能问题。

项目库存预计出货收货 LinkDatetbody> <<tr>11222

ID	新库存
1	Item1	"10">	' 2 '	"5">	"01 - 9 - 2021">	"13">
	第二条	"20">	"3">	"8">	"02 - 9 - 2021">	"18">
	Item3	"30">	"8">	"15">	' 03 - 9 - 2021	"25">
	Item4	"40">	"10">	"20">	"01 - 9 - 2021">	"50">
	Item5	"50">	"25">	"20">	"02 - 9 - 2021">	"45">
	Item6	"60">	"10">	"20">	' 03 - 9 - 2021	"55">

您可以使用FIRST_VALUE()和SUM()窗口函数:

SELECT *, 
FIRST_VALUE(Inventory) OVER (PARTITION BY ID ORDER BY LinkDate) +
SUM(Receipt - ProjectedShipment) OVER (PARTITION BY ID ORDER BY LinkDate) NewInventory
FROM tablename;

查看演示

只使用SUM() OVER

SELECT *, 
SUM(Inventory + Receipt - ProjectedShipment) OVER (PARTITION BY ID ORDER BY LinkDate) NewInventory
FROM tablename;