当前位置: 首页 > 图文教程 > 数据库 > MSSQL > 如何实现SQL Server 2005快速Web分页

MSSQL
精通数据库系列之入门：基础篇1
剖析SQL Server 2005查询通知之基础篇
用sp_lock诊断SQL Sever的性能问题
测试SQL Server业务规则链接方法
解析SQL Server数据应用在不同的数据库中
如何使用SQL Server数据库中查询累计值
逐行扫描 为你讲解几个基本SQLPLUS命令
教你一招：MSSQL数据库索引的应用
确定几个SQL Server栏中的最大值
SQL Server 2005分析服务 统一整合视图
如何使用SQL Server嵌套子查询
用TableDiff产生SQL Server同步脚本
小型商业应用选SQL Server还是Access
用SQL Server Having计算列和平均值
SQL Server 2005基于消息的应用程序介绍
在SQL Server实例之间传输登录和密码
SQL Server 2005深层工具和运行时间集
SQL Server数据库增强版备份体验
使用SQL Server 2000日志转移实现高可用性
浅谈Linq To Sql集成数据库语言的优劣

No. « 32 33 34 35 »

技术文章搜索

关键字

MSSQL 中的 如何实现SQL Server 2005快速Web分页

出处:互联网   整理: 软晨网（RuanChen.com）   发布: 2009-10-17   浏览: 96 ::
收藏到网摘: n/a

分页，就是按照某种规则显示分组数据集，但是在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 SMALLINT
SET @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 + 1
END

列表A

现在数据表中已经具有实例数据。接下来我们看看如何调用程序来实现数据的分页显示。列表B包含这个程序的脚本内容。这个程序含有两个参数：

1.页面大小（给定页面要显示的数据记录数目）。

2.目标页面（返回该页的数据记录）。

以下为引用的内容：

CREATE PROCEDURE usp_SalesRecords
(
      @PageSize FLOAT,
      @TargetPage SMALLINT
)
AS
BEGIN
      WITH Sales_CTE(PageNumber, SaleID, Product, SaleDate, SalePrice)
      AS
      (
SELECT
CEILING((ROW_NUMBER() OVER
ORDER BY SaleDate ASC))/@PageSize) AS PageNumber, SaleID,
Product, SaleDate, SalePrice
FROM SalesHistory FROM SalesHistory
      )

      SELECT
            PageNumber, SaleID, Product, SaleDate, SalePrice
      FROM
            Sales_CTE
      WHERE
            PageNumber = @Targetpage
ENDCREATE PROCEDURE usp_SalesRecords
(
      @PageSize FLOAT,
      @TargetPage SMALLINT
)
AS
BEGIN
      WITH Sales_CTE(PageNumber, SaleID, Product, SaleDate, SalePrice)
      AS
      (
SELECT
CEILING((ROW_NUMBER() OVER
(ORDER BY SaleDate ASC))/@PageSize) AS PageNumber, SaleID,
Product, SaleDate, SalePrice
FROM SalesHistory FROM SalesHistory
      )

      SELECT
            PageNumber, SaleID, Product, SaleDate, SalePrice
      FROM
            Sales_CTE
      WHERE
            PageNumber = @Targetpage
END

列表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
 
执行程序后的返回结果如下：

PageNumber
 SaleID
 Product
 SaleDate
 SalePrice
 
2
 12
 PoolTable
 7/11/1908
0:00
 640
 
2
 15
 PoolTable
 8/11/1908
0:00
 641
 
2
 18
 PoolTable
 9/11/1908
0:00
 658

就如你所看到的，程序执行后将会返回一页的数据，包含三条记录，而且返回的是第二页的数据集。

需要注意的一点

一般来说，有两种方法完成数据结果的分页：在数据库层实现和不在数据库层实现。可以在客户端实现分页，但是这样做的时候，所有的数据都会返回到客户端，而且在进行数据分析的时候就决定了页面数目。在早期版本的SQL Server中，可以在数据库层实现分页，但是需要临时表和表变量。如果上面的例子没有使用CTE来进行分页的话，分页程序就不会那么简单。之所以这么简单就是因为使用了ROW_NUMBER函数的强大功能。