如何从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查询，以获取数据的每个行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应该比上面的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屋！