如何在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中缺少这些函数.

原文链接：https://www.f2er.com/mssql/75115.html

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

解决方法

猜你在找的MsSQL相关文章