How to get previous row value

问题:

How to get a value from previous result row of a SELECT statement

If we have a table called cardevent and has rows [ID(int) , Value(Money) ] and we have some rows in it, for example

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 --- 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

回答1:

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:

select (ID, Value) from table as table1 join
inner join table as table2
on table1.ID = (table2.ID -1)

回答2:

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.

回答3:

You can use LAG() and LEAD() Function to get previous and Next values.

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

回答4:

select t1.value - t2.value from table t1, table t2 
where t1.primaryKey = t2.primaryKey - 1

Try this.