如何在SQL Server中有效地获取上一行和下一行的值

InstrumentID

547
698
708

InstrumentID不是自动生成的列.

假如我在程序中传递参数为698,我应该将之前的值设为547,将下一个值设为708.如何在SQL中有效地执行此操作？

我有这个程序,但效率不高(而且不正确).

Alter PROCEDURE GetNextAndPreviousInsturmentID
(
    @InstrumentID   varchar(14),@PreviousInstrumentID   varchar(14) OUT,@NextInstrumentID   varchar(14) OUT
)
AS
BEGIN
    Declare @RowNum int = 0

    Select @RowNum = ROW_NUMBER() Over (Order by Cast(@InstrumentID as decimal(18))) From Documents Where InstrumentID = @InstrumentID 

    ;With normal As
    (   
        Select ROW_NUMBER() Over (Order by Cast(@InstrumentID as decimal(18))) as RowNum,Cast(InstrumentID as decimal(18)) as InstrumentID
            From Documents 
    )
    Select @PreviousInstrumentID = InstrumentID From normal
        Where RowNum = @RowNum - 1
    Select @NextInstrumentID = InstrumentID From normal
        Where RowNum = @RowNum + 1

END
GO

解决方法

Alter PROCEDURE GetNextAndPreviousInsturmentID
(
    @InstrumentID   varchar(14),@NextInstrumentID   varchar(14) OUT
)
AS
BEGIN

    Declare @Ids TABLE(Id varchar(14))

    ;With normal As
    (   
        --Numerate our rows
        Select ROW_NUMBER() Over (Order by Cast(Documents.InstrumentID as decimal(18)) as RowNumber,Documents.InstrumentID
        From Documents 

    )
    --Insert three rows from our table with our id and previos/next id
    INSERT INTO @Ids(Id)
    SELECT TOP(3) normal.InstrumentID
    FROM normal
    WHERE RowNumber >=
        (
            SELECT RowNumber - 1
            FROM normal
            WHERE normal.InstrumentID = @InstrumentID
        )
    ORDER BY normal.RowNumber
    --select next and previos ids

    SELECT @PreviousInstrumentID = Min(CAST(Id as decimal(18))),@NextInstrumentID = MAX(CAST(Id as decimal(18)))
    FROM @Ids
END
GO

在MS SQL 2012中,我们有新的窗口函数,如FIRST_VALUE和LAST_VALUE,遗憾的是在sql 2008中缺少这些函数.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!