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

常常写SQL查询剧本的伴侣，很也许会遭遇分页查询的题目。在MSSQL2000及早年的版本中，分页的写法大多回收纯Top嵌套方法，写法较量伟大、并且服从并不抱负。

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

从MSSQL2012开始，SQL Server提供了offset要领举办分页。行使offset startPage rows fetch next pageSize rows only 方法举办分页。

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

ROW_NUMBER实现对功效集的输出举办编号。 详细来说，返回功效集分区老手的序列号，每个分区的第一行从 1 开始。

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 

（编辑：河北网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!