如何选择前一行值? [英] 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 和值的组合是唯一的(您必须指定什么将您的排序强加于表，以便人们知道什么是前一行)，你必须使用这个查询:

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