英文:
Sum previous row values in same table
问题
| ItemID | Qty | 库存余量（更新为此列的结果）| -------- | -------- |---------------| 1000 | 1 | 1| 1000 | 5 | 6（前一行的数量之和加上当前行的数量）| 1000 | 2 | 8（前一行的数量之和加上当前行的数量）| 1000 | 1 | 9（前一行的数量之和加上当前行的数量）
英文:
I try to sum value in pervious rows, I want to sum the pervious quantity "Stock on hand" and put the result in each row, example
| ItemID | Qty | Stockon Hand ( the result updated in this column) |
|---|---|---|
| 1000 | 1 | 1 |
| 1000 | 5 | 6 ( sum qty previous in pervious row plus the qty in the current row) |
| 1000 | 2 | 8 ( sum qty previous in pervious row plus the qty in the current row) |
| 1000 | 1 | 9 ( sum qty previous in pervious row plus the qty in the current row) |
How can I update the column "Stock on hand" by summing the qty in the current row and previous rows?
select ItemID, Qty, sum(qty) over (order by itemid rows between 1 preceding and 1 preceding) as previous_Qtyfrom #Stockorder by itemid
答案1
得分: 1
这是一个可行的示例。请注意，我在列名中添加了一个适当的序列ID，但您也可以使用日期列。
您还可以注意到我添加了 partition by ItemID
Declare @YourTable Table ([ID] int,[ItemID] int,[Qty] int)Insert Into @YourTable Values(1,1000,1),(2,1000,5),(3,1000,2),(4,1000,1)Select *,OnHand = sum(Qty) over (partition by ItemID order by ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)from @YourTable
结果
ID ItemID Qty OnHand1 1000 1 12 1000 5 63 1000 2 84 1000 1 9
英文:
Here is a working example. Note, I took the liberty of adding an column for a proper sequence... ID, but you could use a date column as well
You may also notice that I added partition by ItemID
Declare @YourTable Table ([ID] int,[ItemID] int,[Qty] int)Insert Into @YourTable Values(1,1000,1),(2,1000,5),(3,1000,2),(4,1000,1)Select *,OnHand = sum(Qty) over (partition by ItemID order by ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)from @YourTable
Results
ID ItemID Qty OnHand1 1000 1 12 1000 5 63 1000 2 84 1000 1 9
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论