《如何在SQL Server查询中实现高效分页》由会员分享，可在线阅读，更多相关《如何在SQL Server查询中实现高效分页（6页珍藏版）》请在金锄头文库上搜索。
1、如何在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_NUM
2、BER的含义及语法定义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 的顺
3、序。 order by 子句是必选项。返回值:bigint。结果集分区内行的序列号。offset的含义及语法定义offset是order by的子句，主要用来限定返回的行数，用来做分页也是很合适的。只是从MSSQL2012才开始支持。语法结构如下:1. FETCHNEXTinteger_constant|fetch_row_count_expressionROWSONLYfetch_row_count_expression 可以是变量、参数或常量标量子查询。 在使用子查询时，它无法引用在外部查询范围中定义的任何列。也就是说，它无法与外部查询相关联。结合到分页，语法语法:1. offsetsta
4、rtPagerowsfetchnextpageSizerowsonly其中起始页面:startPage=(page-1)*rows，页面大小:pageSize=rows演示数据准备为了说明方便，我们准备一些演示数据，这是一个简单的业务销售表，字段只有业务员、销售区域和销售额，如下: http:/1. declaresaletable(FNamenvarchar(50),FDistrictnvarchar(50),FAmountdecimal(28,10);2. insertintosalevalues(张三,北京,20000),(张三,上海,50000),(张三,深圳,40000),(张三,广
5、州,30000),(李四,北京,30000),(李四,上海,50000),(李四,深圳,40000),(李四,广州,10000),(王二,北京,70000),(王二,上海,10000),(王二,深圳,60000),(王二,广州,20000),(马六,北京,80000),(马六,上海,20000),(马六,深圳,70000),(马六,广州,60000)准备演示数据使用ROW_NUMBER分页比如我们希望按照业务员+销售区域排序后，每4条记录一页显示，写法如下:1. declarepagesizeint=4;-每页记录数2. declarepagenumint=1;-第几页3. selectv.*
6、from(selectrow_number()over(orderbyFName,FDistrict)asFRowIndex,*fromsale)asvwherev.FRowIndexbetweenpagesize*(pagenum-1)+1andpagenum*pagesize;分页查询ROW_NUMBER函数在SQL中属于热名称(即刚定的名称FRowIndex)，只可以出现在select子句中，需要放在子查询中。也可以先对子查询做好定义后面再直接引用，语法如下: http:/1. declarepagesizeint=4;-每页记录数2. declarepagenumint=1;-第几页3
7、. withsaledataas(selectrow_number()over(orderbyFName,FDistrict)asFRowIndex,*fromsale)4. select*fromsaledatawhereFRowIndexbetweenpagesize*(pagenum-1)+1andpagenum*pagesize;分页查询ROW_NUMBER来做分页查询，经过反复应用测试，效率还是很高的。完整的测试脚本参看下图:使用OFFSET实现分页重复的代码部分不再赘述，在查询时要注意，offset是Order By的子句，不能独立存在。语法结构如下:1. select*fromsaleorderbyFName,FDistrictoffset(pagenum-1)*pagesizerowsfetchnextpagesizerowsonly http:/f-1.cc返回的结果与使用row_number是一致的。完整的测试脚本参看下图:希望对您有所帮助!
