SQL 如何获取上一行值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4860024/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get previous row value
提问by Gopal
How to get a value from previous result row of a SELECT statement
如何从 SELECT 语句的前一个结果行获取值
If we have a table called cardevent and has rows [ID(int) , Value(Money) ] and we have some rows in it, for example
例如，如果我们有一个名为 cardevent 的表并且有行 [ID(int) , Value(Money) ] 并且我们有一些行，例如
ID --Value
1------70
1------90
2------100
2------150
2------300
3------150
3------200
3-----250
3-----280
so on...
很快...
How to make one Query that get each row ID,Value and the previous Row Value in which data appear as follow
如何制作一个查询，获取每行 ID、值和前一行值，其中数据显示如下
ID --- Value ---Prev_Value
1 ----- 70 ---------- 0
1 ----- 90 ---------- 70
2 ----- 100 -------- 90
2 ------150 -------- 100
2 ------300 -------- 150
3 ----- 150 -------- 300
3 ----- 200 -------- 150
3 ---- 250 -------- 200
3 ---- 280 -------- 250
so on.
很快。
So can anyone help me to get the best solution for such a problem ?
那么任何人都可以帮助我获得解决此类问题的最佳解决方案吗？
Need Query Help
需要查询帮助
采纳答案by Roy T.
You would have to join the table with itself, I'm not sure if this is 100% legitimate SQL, but I have no SQL-Server to try this at the moment, but try this:
您必须将表与自身连接，我不确定这是否是 100% 合法的 SQL，但我目前没有 SQL-Server 可以尝试此操作，但请尝试以下操作：
select (ID, Value) from table as table1 join
inner join table as table2
on table1.ID = (table2.ID -1)
回答by sqlRookie
SELECT t.*,
LAG(t.Value) OVER (ORDER BY t.ID)
FROM table AS t
SELECT t.*,
LAG(t.Value) OVER (ORDER BY t.ID)
FROM table AS t
This should work. The Lag function gets the previous row value for a specific column. I think this is what you want here.
这应该有效。Lag 函数获取特定列的前一行值。我想这就是你想要的。
回答by Bharatkmr
You can use LAG() and LEAD() Function to get previous and Next values.
您可以使用 LAG() 和 LEAD() 函数来获取上一个和下一个值。
SELECT t.Value OVER (ORDER BY t.ID) PREVIOUS_VALUE,
t.value VALUE,
LEAD(t.value) OVER (ORDER BY t.ID) NEXT_VALUE
FROM TABLE T
GO
回答by YC1207
select t1.value - t2.value from table t1, table t2
where t1.primaryKey = t2.primaryKey - 1
Try this.
尝试这个。
