I'm working in SQL Server 2014 Management Studio.
我在SQL Server 2014 Management Studio工作。
Not really sure how to explain this but it's best if I just explain with an example.
我不太清楚该如何解释这个问题，但最好还是用一个例子来解释。
So I've figured out how to get the next lowest ID, that is fairly simple. But once i get that row i need to take the value from it and apply it to the next highest value.
我已经知道如何得到下一个最低的ID，这很简单。但是一旦我得到了这一行，我就需要取它的值并把它应用到下一个最高的值。
If I have 4 rows
如果有4行
ID value
-------------
10 50
30 200
20 75
25 100
I want to take the value each row and applying to the row with the next highest ID. So it should look like this.
我想取每一行的值然后应用到下一个ID最高的那一行，它应该是这样的。
ID value
-------------
10 null or 0
30 100
20 50
25 75
Since there is no row before 10 ID, that row should have a value of null or 0, doesn't matter. And the others should just follow the pattern of taking the value from the row with the next lowest ID.
因为在10 ID之前没有行，所以行应该具有null或0的值，这并不重要。而其他的则应该遵循使用下一个最低ID从行获取值的模式。
1 个解决方案
#1
6
You're looking for LAG():
你正在寻找滞后():
Select Id, Lag(Value) Over (Order By Id) As Value
From YourTable;
Working demo:
工作演示:
Declare @YourTable Table
(
Id Int,
Value Int
);
Insert @YourTable
Values (10, 50), (30, 200), (20, 75), (25, 100);
Select Id, Lag(Value) Over (Order By Id) As Value
From @YourTable;
Results
结果
Id Value
10 NULL
20 50
25 75
30 100
#1
6
You're looking for LAG():
你正在寻找滞后():
Select Id, Lag(Value) Over (Order By Id) As Value
From YourTable;
Working demo:
工作演示:
Declare @YourTable Table
(
Id Int,
Value Int
);
Insert @YourTable
Values (10, 50), (30, 200), (20, 75), (25, 100);
Select Id, Lag(Value) Over (Order By Id) As Value
From @YourTable;
Results
结果
Id Value
10 NULL
20 50
25 75
30 100