分页，就是按照某种规则显示分组数据集，但是在SQL Server 中，分页并不是十分容易就能够实现。在过去，开发人员通常需要自己编写程序，使用临时表格来实现分页功能，或者将所有的数据结果集返回到客户端，在客户端进行分页操作。从开发人员或者DBA的角度来看，两种方法都不能令人满意。
随着SQL Server的发布，其中的一些排序函数使得开发人员编写数据分页程序变得更加简单和高效。这些新的排序函数提供了统计数据集的数目，对数据集归类，按照某种标准对数据集排序等功能。在这篇文章中，我将着重介绍新增加的ROW-NUMBER排序函数，它会根据你指定的分类标准将结果数据集进行分类，同时给数据集分配连续的页面。
一个分页的实例
我总是喜欢通过例子来介绍如何使用新技术，所以让我们来看看如何设计一个存储程序，使用ROW_NUMBER这一新函数来实现数据的自动分页。
首先，需要定义一些数据结构。我们定义一个SalesHistory表格，它包含的数据是我们在网上售出产品的销售记录。包括一些常见的销售信息，例如，所售产品、售出日期、产品售出价格等。下面的脚本就是创建这样的一个表格：
以下为引用的内容： IF OBJECT_ID('SalesHistory','U') > 0 DROP TABLE SalesHistory CREATE TABLE SalesHistory( SaleID INT IDENTITY(1,1), Product VARCHAR(30), SaleDate SMALLDATETIME, SalePrice MONEY ) |
运行列表A中的脚本则在上面创建的SalesHistory表中添加一些例子数据。
以下为引用的内容： DECLARE @i SMALLINTSET @i = 1 WHILE (@i <=100)BEGIN INSERT INTO SalesHistory (Product, SaleDate, SalePrice) VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57) ) INSERT INTO SalesHistory (Product, SaleDate, SalePrice) VALUES ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13) ) INSERT INTO SalesHistory (Product, SaleDate, SalePrice) VALUES ('PoolTable', DATEADD(mm, @i, '3/11/1908'),DATEPART(ms, GETDATE()) + (@i + 29) ) SET @i = @i + 1END |
列表A
现在数据表中已经具有实例数据。接下来我们看看如何调用程序来实现数据的分页显示。列表B包含这个程序的脚本内容。这个程序含有两个参数：
1.页面大小（给定页面要显示的数据记录数目）。
2.目标页面（返回该页的数据记录）。
以下为引用的内容： CREATE PROCEDURE usp_SalesRecords( @PageSize FLOAT, @TargetPage SMALLINT)ASBEGIN WITH Sales_CTE(PageNumber, SaleID, Product, SaleDate, SalePrice) AS (SELECTCEILING((ROW_NUMBER() OVERORDER BY SaleDate ASC))/@PageSize) AS PageNumber, SaleID,Product, SaleDate, SalePriceFROM SalesHistory FROM SalesHistory ) SELECT PageNumber, SaleID, Product, SaleDate, SalePrice FROM Sales_CTE WHERE PageNumber = @TargetpageENDCREATE PROCEDURE usp_SalesRecords( @PageSize FLOAT, @TargetPage SMALLINT)ASBEGIN WITH Sales_CTE(PageNumber, SaleID, Product, SaleDate, SalePrice) AS (SELECTCEILING((ROW_NUMBER() OVER(ORDER BY SaleDate ASC))/@PageSize) AS PageNumber, SaleID,Product, SaleDate, SalePriceFROM SalesHistory FROM SalesHistory ) SELECT PageNumber, SaleID, Product, SaleDate, SalePrice FROM Sales_CTE WHERE PageNumber = @TargetpageEND |
列表B
如果你刚刚开始使用SQL Server，可能会不熟悉以"WITH"开头的声明语句。这条语句会调用SQL Server中的一个新属性，我们称之为common table expression（CTE），从本质上来说，我们可以将CTE看作是高版本的临时表。
分页的实质就是CTE中的TSQL语句。在下面的选择语句中，我使用了一个新的排序函数——ROW_NUMBER（这一函数很容易使用，你只需要给ROW_NUMBER函数提供一个域名作为参数，ROW_NUMBER会用它来进行分页）。随后，我使用@PageSize参数来划分每页的行数以及每页的最大行数值。
例如，假设现在有一个包含三条记录的数据集，并设计每页显示两条记录，那么头两条记录将会在第一页显示，因为每页的行数必须小于或者等于第一个变量值。第三条记录将会在第二页显示，因为每页的可显示最大行数值应该小于2但是又大于1。
可以使用下面的脚本调用存储程序：
以下为引用的内容： EXECUTE usp_SalesRecords @PageSize = 3, @TargetPage = 2 关键词标签：SQL Server 2005
热门文章
人气排行 11配置和注册ODBC数据源-odbc数据源配置教程 22如何远程备份（还原）SQL2000数据库 33SQL2000数据库远程导入（导出）数据 44SQL2000和SQL2005数据库服务端口查看或修改 55修改Sql Server唯一约束教程 66SQL Server 2005降级到2000的正确操作步骤 77sql server系统表损坏的解决方法 88浅谈JSP JDBC来连接SQL Server 2005的方法
|
SqlServer2005对现有数据进行分区具体步骤
sql server系统表损坏的解决方法
MS-SQL2005服务器登录名、角色、数据库用户、角色、架构的关系
查看所有0条评论>>