转载

OFFSET & FETCH

OFFSET & FECTH 关键字与ORDER BY结合使用，实现对查询结果的分页

一·单独使用OFFSET:

示例：查询所有职员的信息，按雇佣日期排序并跳过前285条记录（共290条）

1 USE AdventureWorks2014 2 SELECT [BusinessEntityID],[HireDate],[JobTitle] 3 FROM [HumanResources].[Employee] 4 ORDER BY [BusinessEntityID] 5 OFFSET 285 ROWS

查询结果：

BusinessEntityID HireDate JobTitle 1 286    2013-05-30    Sales Representative 2 287    2012-04-16    European Sales Manager 3 288    2013-05-30    Sales Representative 4 289    2012-05-30    Sales Representative 5 290    2012-05-30    Sales Representative

二、OFFSET和FECTH结合使用：

示例：查询所有职员的信息，按雇佣日期排序并跳过前10条记录再显示之后的5条记录

1 USE AdventureWorks2014 2 SELECT [BusinessEntityID],[HireDate],[JobTitle] 3 FROM [HumanResources].[Employee] 4 ORDER BY [BusinessEntityID] 5 OFFSET 10 ROWS 6 FETCH NEXT 5 ROWS ONLY

查询结果：

BusinessEntityID HireDate JobTitle 1 11    2010-12-05    Senior Tool Designer 2 12    2007-12-11    Tool Designer 3 13    2010-12-23    Tool Designer 4 14    2010-12-30    Senior Design Engineer 5 15    2011-01-18    Design Engineer

正文到此结束