mssql sqlserver 如何查询某条记录的相邻2条记录呢?


摘要:
下文讲述sqlserver使用脚本的方式查询相邻2条记录的方法分享,如下所示:
实验环境:sql server 2008 R2



实现思路:
采用cte表达式,将当前记录行按照指定规则,进行排序操作,然后获取当前前和后的各一行记录。

  CREATE TABLE [maomao365.com]
(keyId INT IDENTITY(1,1),
 info  NVARCHAR(105))

INSERT INTO [maomao365.com]
(info)
SELECT N'sqlserver爱好者' UNION ALL
SELECT N'猫猫小屋' UNION ALL
SELECT N'最爱sql' UNION ALL
SELECT N'编程爱好者' UNION ALL
SELECT N'其它'  
GO

 
DECLARE @i INT
SET @i=3;

WITH cte1
AS
(SELECT keyId,info,'max' AS extend FROM [maomao365.com] WHERE keyId BETWEEN @i-2 AND @i-1
UNION ALL 
SELECT  keyId,info,'min' extend   FROM [maomao365.com] WHERE keyId BETWEEN @i+1 AND @i+2
)

SELECT keyId,info  
FROM cte1; 
go

truncate table [maomao365.com]
drop     table [maomao365.com]
go