说我有这些行,
InstrumentID
547 698 708
假如我在程序中传递参数为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@H_403_14@
解决方法
试试这个：
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中缺少这些函数.
@H_403_14@ @H_403_14@ 原文链接：https://www.f2er.com/mssql/75115.html