如何从 SELECT 语句的前一个结果行获取值? [英] How to get a value from previous result row of a SELECT statement?

问题描述

如果我们有一个名为 FollowUp 的表并且有行 [ ID(int) , Value(Money) ]我们有一些行，例如
ID --值
1------70
2------100
3------150
8------200
20-----250
45-----280
并且我们想要创建一个 SQL Query 来获取每一行 ID、值和前一行值，其中数据显示如下
ID --- 值 ---Prev_Value
1 ----- 70 ---------- 0
2 ----- 100 -------- 70
3 ----- 150 -------- 100
8 ----- 200 -------- 150
20 ---- 250 -------- 200
45 ---- 280 -------- 250

If we have a table called FollowUp and has rows [ ID(int) , Value(Money) ] and we have some rows in it, for example
ID --Value
1------70
2------100
3------150
8------200
20-----250
45-----280
and we want to make one SQL 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
2 ----- 100 -------- 70
3 ----- 150 -------- 100
8 ----- 200 -------- 150
20 ---- 250 -------- 200
45 ---- 280 -------- 250

我进行了以下查询，但我认为它在大量数据中的性能很差

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

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

那么谁能帮我找到解决此类问题的最佳解决方案?

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

推荐答案

这个 sql 应该比你上面的那个执行得更好，尽管这些类型的查询往往有点性能密集......所以你可以输入任何东西它们限制您正在查看的数据集的大小将非常有帮助.例如，如果您正在查看特定日期范围，请将其输入.

This sql should perform better then the one you have above, although these type of queries tend to be a little performance intensive... so anything you can put in them to limit the size of the dataset you are looking at will help tremendously. For example if you are looking at a specific date range, put that in.

SELECT followup.value, 
    ( SELECT TOP 1 f1.VALUE 
      FROM followup as f1 
      WHERE f1.id<followup.id 
      ORDER BY f1.id DESC
    ) AS Prev_Value
FROM followup

HTH

这篇关于如何从 SELECT 语句的前一个结果行获取值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！