LEAD、LAG

DECLARE @Gamer TABLE
(
  Id INT IDENTITY(1, 1) PRIMARY KEY ,
  [Name] NVARCHAR(50) ,
  Gender NVARCHAR(10) ,
  GameScore INT
)

INSERT  INTO @Gamer VALUES  ( 'Name01', 'Male', 1500 );
INSERT  INTO @Gamer VALUES  ( 'Name02', 'Male', 2600 );
INSERT  INTO @Gamer VALUES  ( 'Name03', 'Male', 3500 );
INSERT  INTO @Gamer VALUES  ( 'Name04', 'Female', 1500 );
INSERT  INTO @Gamer VALUES  ( 'Name05', 'Female', 3350 );
INSERT  INTO @Gamer VALUES  ( 'Name06', 'Female', 3350 );
INSERT  INTO @Gamer VALUES  ( 'Name07', 'Female', 3500 );
INSERT  INTO @Gamer VALUES  ( 'Name08', 'Male', 3500 );
INSERT  INTO @Gamer VALUES  ( 'Name09', 'Male', 3450 );
INSERT  INTO @Gamer VALUES  ( 'Name10', 'Male', 2500 );

SELECT  * ,
        LEAD(GameScore, 2, -4) OVER ( ORDER BY GameScore ) AS [LEAD(GameScore,2,-1)] ,
        LAG(GameScore, 1, -1) OVER ( ORDER BY GameScore ) AS [LAG(GameScore,1,-1)]
FROM    @Gamer