SQL SERVER 分页方法

9/1/2015来源:SQL技巧人气:1990

SQL SERVER 分页方法

最近项目中需要在SQL SERVER中进行分页,需要编写分页查询语句。之前也写过一些关于分页查询的语句,但是性能不敢恭维。于是在业务时间,在微软社区Bing了一篇老外写的关于SQL SERVER分页的文章。看过之后,感觉自己之前写的语句,太低端,太不科学了。 文章中讲了两种分页方法,其中一种只适用于SQL SERVER2012以上版本。

ROW_NUMBER()函数分页

先介绍一下ROW_NUMBER()函数,这个函数的主要作用,从它的命名中就可看出来。ROW,每列,NUMBER数字,它的作用就是为每行分配一个数字。但是它一般不单独使用。它的语法是这样的:

image

PARTITION BY value_exPRession: 这个参数是通过value_expression,把我们查询到结果集给分成若干区。举个例子,我们有一张成绩表。我们要为女生、男生在各自性别内按成绩排名次。这时,我们就可以通过PARTITTION BY GENDER(性别字段) 将全班人员分为两个区,女生区、男生区。男生在男生区排名次,女生在女生区排名次。

order_by_clause:这个是就是order by 语句,将数据集按某个字段进行排序。

需要注意的是,PARTITION BY value_expression不是必要参数,但是order_by_clause是必要参数。要使用ROW_NUMBER()必须要有order by 语句。下面给出几个ROW_NUMBER() 实际使用场景:

1.

image

这个语句把表中TerritoryName不为空、SalesYID不为空的数据按字段SalesYTD降序排序,再通过ROW_NUMBER()函数为每行分配一个连续的数字,将数字存入新添加的一个名为Row的字段中。结果如下:

image

2.这种情况,就是我们今天要讲的分页方法。(Returning a subset of rows)

image

在这个场景中,我们用到了Comoon table expression(中间表表达式),它的作用与临时表差不多。就是将查询到的结果放入一个地方,供再次查询。对Common Table Expression感兴趣的可以去MSDN上学习。原文地址:http://msdn.microsoft.com/en-us/library/ms175972.aspx.

我们使用ROW_NUMBER()函数为查询的结果每行数据分配一个数字,将数字放入RowNumber列中(通过AS 生成的新列)。再将数据集放入中间表OrderedOrders中。查询中间表,这个时候我们就可以使用Where RowNumber BETWEEN A AND B.来读取从A条到B条的数据了,就可以达到我们的分页需求了.

3.Using ROW_NUMBER() with PARTITION

image

在这个场景中,我们用到了PARTITION参数。我们按区域(territoryName)将查询结果进行分区,再在分过区的数据集中以SalesYTD降序,再为降序后的每行数据分配数字。最后以TerritoryName字段升序。结果如下图:

image

关于ROW_NUMBER()的原文地址:http://msdn.microsoft.com/en-us/library/ms186734.aspx.

这种分页方法的主要思想,就是通过ROW_NUMBER()为每行生成标识。再通过BETWEEN AND 语句来获取当前页的数据,已达到分页的作用。

image

我们创建了一个表名TB.EXMPLE的表,添加了1,000,000条数据。我们通过这种分页方法来编写如下查询语句:

-----查看每页显示10条,第2页的数据    DECLARE @PageNumber AS INT,@RowNumber AS INTSET @PageNumber=2SET @RowNumber=5SELETCT * FROM (         SELECT ROW_NUMBER() OVER(ORDER BY ID_EXMPLE) AS NUMBER,           * FROM TB_EXAMPLE        )AS TBLWHERE NUMBER BETWEEN ((@PageNumber-1)*@RowNumber+1)  AND (@PageNumber*@RowNumber)

结果如下:

image

2.OFFSET AND FETCH 分页

offset and fetch,这是SQL SERVER 2012新添加的功能。2012以下版本,不支持。

DECALER @PageNumber AS INT,@RowNumber AS INTSET @PageNumber=2SET @RowNumber=5SELECT * FROM TB_EXAMPLEWHERE OFFSET ((@PageNumber-1)*@RowNumber) ROWSFETCH NEXT @RowNumber ROWS ONLY;

OFFSET A ROWS ,将前A条记录舍去,FETCH NEXT B ROWS ,向后在读取B条数据。

原文地址如下:http://social.technet.microsoft.com/wiki/contents/articles/23811.paging-a-query-with-sql-server.aspx.