如何选择上一个行值? [英] HOW TO SELECT PREVIOUS ROW VALUE?

问题描述

如何从SELECT语句的前一个结果行中获取值

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

如果我们有一个名为cardevent的表，并且有[ID(int)，Value(Money)]行，并且其中有一些行，例如

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 

等等...

如何进行查询以获取每个行的ID，值和前一个行值，其中数据如下所示

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 

等等.

我进行以下查询，但在海量数据中的性能是如此糟糕

i make the following query but it's so bad in performance in huge amount of data

SELECT cardevent.ID, cardevent.Value, 
      (SELECT F1.Value
       FROM cardevent as F1 
       where F1.ID = (SELECT  Max(F2.ID)
                      FROM cardevent as F2 
                      WHERE F2.ID < cardevent.ID)
      ) AS Prev_Value
FROM cardevent

那么有人可以帮助我获得针对此类问题的最佳解决方案吗?

So can anyone help me to get the best solution for such a problem ?

推荐答案

这对于两个数据库都可以正常工作:

This one should work fine for both databases:

SELECT cardevent.ID, cardevent.Value, 
  (SELECT TOP 1 F1.Value
   FROM cardevent as F1
   WHERE F1.ID < cardevent.ID
   ORDER BY F1.ID DESC
   ) AS Prev_Value
FROM cardevent

更新:假设ID不是唯一的，但是ID和Value的组合是唯一的(您必须指定在表上强加顺序的内容，所以人们知道什么是 the上一行)，则必须使用以下查询:

Update: Assuming that ID is not unique, but that the combination of ID and Value is unique (you must specify what imposes your ordering on the table, so one knows what is the previous row), you must use this query:

select cardevent.ID, cardevent.Value, 
    (select TOP 1 F1.Value from cardevent as F1
     where (F1.ID < cardevent.ID) or (F1.ID = cardevent.ID and F1.Value < cardevent.Value)
     order by F1.ID DESC, F1.Value DESC
    ) AS Prev_Value
from cardevent 

这篇关于如何选择上一个行值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！