如何实现SQL Server 2005快速Web分页
作者:Tim Chapman 发布时间:2009-01-21 14:51:00
分页,就是按照某种规则显示分组数据集,但是在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函数的强大功能。
猜你喜欢
- 这是一篇关于使用JScript RuntimeObject(MSDN)调试的文章。虽然这些例子中的大多数在其他浏览器中不能运行,但在IE 5
- 在印刷排版中“point”是一个绝对的单位,它等于 1/72 英寸。可以用尺子丈量的,物理的英寸。但在CSS中pt的含义
- 很简单,我们用两个文件就可以实现这一功能:login.htm<% @ Language=JavaScript&nb
- 本文介绍了使用XMlhttp技术来生成html页面,值得借鉴。相关函数:<% ’定义xmlhttp funct
- select * from V$NLS_PARAMETERS; SQL> alter session set NLS_DATE_LAN
- 一定要对用户可能输入的诸如引号,尖括号等特殊字符给予足够重视,它们可能引发严重的安全问题。SQL注入的基本手法之一,就是利用对单引号未加过滤
- 使用sql语句删除数据库中重复记录的两个方法如下:方法一declare @max integer,@id in
- 在使用ASP来进行后端的数据合法性校验的时候,有些人为满足不同环境下面的数据校验,编写了很多的函数来实现,比如,我们想要校验用户输入的URL
- 我要说的内容都是非常基础的内容,高手就免看了,如果看了欢迎给点意见啊。新手或者对低层还不是很了解的人可以看看,帮助理解与记忆。XMLHttp
- 我们平常在网页上显示的字体最小一般是12PX,当小于10PX时,显示的效果就大打折扣了,因为中文默认的字体是宋体,当小于12PX时的效果如下
- mysql是linux平台下最流行的数据库系统,今天介绍的是mysql的安装及简单的操作方法!groupadd mysql //建立mysq
- 问题:如何保护自己的ASP源代码不泄露? 答:下载微软的Windows Script Encoder,对ASP的脚本和客户端javascri
- 看了cragle的《有没有必要将网站Div+Css重构?》的文章,有一些想法不说不快,我也在文章的评论里提到曾经开除过两个执着使用div技术
- 通过亲密性原则,我们可以将一个页面中的元素按照某种逻辑理解上的差异划分成不同的元素组合;再通过对齐原则,使这些不同的元素组合在视觉上看起来彼
- 在SQL Server开发问题中你可能会问到的十个问题:1、什么是常见的对表和字段的名字约束?2、有没有可能在不了解T-SQL的情况下编写存
- 这带来了很多好处,但是也要求学习一些新的概念、命令、使用和管理任务。所以在投入你的生产系统之前,看看它解决什么、管理什么,以及对它的正反面评
- 我们工作中经常需要将数据转化成柱状图,饼图等,以方便直观的分析数据, 这里给大家介绍一个ASP中制作饼图、柱状图的组件:csDra
- 写在前面的话:此篇还是asp相关的,相信玩ASP的都有这个感觉,当数据有5万多条时-------just like音乐网,要调用最新的10条
- 无意中看到以前在电脑上保存的一个html页面,关于div水平垂直居中的问题。如何实现div水平垂直居中呢?1.已知宽高度水平垂直居中posi
- 其实这里的静态页面并不是真正意义上的静态,但可以达到了静态页面的解析效率,还未经项目测试,拿来分享。代码如下:<% Cons