数据库大师成长日记:如何在SQL Server查询中实现高效分页

从MSSQL2005开始，SQL Server提供了一个内置函数 ROW_NUMBER ，这是一个非常神奇的函数。

从MSSQL2012开始，SQL Server提供了 offset 方法进行分页。使用offset startPage rows fetch next pageSize rows only 方式进行分页。

今天我们就谈谈ROW_NUMBER和offset的语法和在分页中的应用。

ROW_NUMBER实现对结果集的输出进行编号。 具体来说，返回结果集分区内行的序列号，每个分区的第一行从 1 开始。

ROW_NUMBER ( ) OVER ( [ PARTITION BY 字段 , ] order_by_clause )PARTITION BY:将 FROM 子句生成的结果集划分为应用 ROW_NUMBER 函数的分区。 value_expression 指定对结果集进行分区所依据的列。 如果未指定 PARTITION BY，则此函数将查询结果集的所有行视为单个组。order_by_clause: 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。 order by 子句是必选项。返回值:bigint。结果集分区内行的序列号。offset的含义及语法定义

offset是order by的子句，主要用来限定返回的行数，用来做分页也是很合适的。只是从MSSQL2012才开始支持。语法结构如下:

FETCH {  NEXT } { integer_constant | fetch_row_count_expression } {  ROWS }  ONLY 

fetch_row_count_expression 可以是变量、参数或常量标量子查询。 在使用子查询时，它无法引用在外部查询范围中定义的任何列。也就是说，它无法与外部查询相关联。

结合到分页，语法语法:

offset startPage rows  fetch  next pageSize  rows  only 

其中起始页面:startPage=(@page-1)*@rows，页面大小:pageSize=@rows

为了说明方便，我们准备一些演示数据，这是一个简单的业务销售表，字段只有业务员、销售区域和销售额，如下:

declare @sale  table( FName nvarchar(50), FDistrict nvarchar(50), FAmount  decimal(28,10) ); 
insert  into @sale  values ('张三','北京',20000), ('张三','上海',50000), ('张三','深圳',40000), ('张三','广州',30000), ('李四','北京',30000), ('李四','上海',50000), ('李四','深圳',40000), ('李四','广州',10000), ('王二','北京',70000), ('王二','上海',10000), ('王二','深圳',60000), ('王二','广州',20000), ('马六','北京',80000), ('马六','上海',20000), ('马六','深圳',70000), ('马六','广州',60000) 

比如我们希望按照业务员+销售区域 排序 后，每4条记录一页显示，写法如下:

declare @pagesize  int =4;--每页记录数  
declare @pagenum  int =1;--第几页  
select v.*  from ( select row_number() over( order  by FName,FDistrict)  as FRowIndex,*  from @sale)  as v  where v.FRowIndex  between @pagesize*(@pagenum-1)+1  and @pagenum*@pagesize; 

ROW_NUMBER函数在 SQL 中属于热名称(即刚定的名称FRowIndex)，只可以出现在select子句中，需要放在子查询中。也可以先对子查询做好定义后面再直接引用，语法如下:

declare @pagesize  int =4;--每页记录数  
declare @pagenum  int =1;--第几页  
with saledata  as (  select row_number() over( order  by FName,FDistrict)  as FRowIndex,*  from @sale )  
select *  from saledata  where FRowIndex  between @pagesize*(@pagenum-1)+1  and @pagenum*@pagesize; 

ROW_NUMBER来做分页查询，经过反复应用测试，效率还是很高的。完整的测试脚本参看下图:

重复的代码部分不再赘述，在查询时要注意，offset是Order By的子句，不能独立存在。语法结构如下:

select *  from @sale  order  by FName,FDistrict offset (@pagenum-1)*@pagesize  rows  fetch  next @pagesize  rows  only 

返回的结果与使用row_number是一致的。完整的测试脚本参看下图: