SQL Server 与Access数据库相关分页技术
当数据库中的数据量很大时,一般从数据库中获取数据列表时,要求进行分页显示,尤其是在网络中.
经典的数据分页方法是:ADO 纪录集分页法,也就是利用ADO自带的分页功能(利用游标)来实现分页。现在流行的分页方法一般是检索页面大小的块区的数据,而非检索所有的数据,然后单步执行当前行。
1.第一种,利用top 及not in进行分页。
select top @PageSize * from TableName where [PK] not in(select top @RecordStart [pk] from jobs ) order by pk asc/desc。
@PageSize是每一页显示的条数。
TableName是要求分页的表或者视图,PK是一个唯一字段。
@RecordStart 是记录开始的位置.根据每一页多少条,与第几页,算出来的.具体算法一般为:@RecordStart=PageSize*(PageNums-1)。
该分页算法有以下主要问题,如果用非主键字段(或者非唯一字段)排序,再进行分页时,导致取出来的数据不正确。并且效率不高。
2. 第二种,利用top 及top子句来进行分页。
select top @PageSize * from ( select top @totalRecord-@startpos * from tablename order by [pk] desc,@sortfield )order by a.@sortfield
@PageSize是每一页的记录数。
@totalRecord是总记录数,
@startPos 是记录开始的数。比如说第2页,每一页20条就是20*(2-1)。
@pk,表或者视图的主键。
@sortField要求进行排序的字段。
该算法是对第一种算法进行改进。可以对非唯一字段进行排序,但是要求每一次都给出表的主键。主键是固定的,在数据量大时,缺点也是效果不够高。
3. 第三种,利用top(max) 及top min来进行分页。也就是我们现在通用的分页算法,及分页存储过程的算法。
select top @ PageSize * from (@strsql) a where @pk>(select max(@pk) from (select top @startpos @pk from (@strsql) order by @pk ) a) and @pk<=(select min(@pk) from select top @endpos @pk from (@strsql) order by @pk) a) order by @pk
@PageSize 每一页的条数。
@strsql,即选择数据的语句,如select * from aa。
@pk,即主键,或者唯一字段。
@startpos.分页下标。@startpos=(@pageNums)*(@page-1)
@endpos。分页上标 @endpos=(@pageNums*@page)-1;
该算法要求根据一个唯一字段来进行分页,一般用主键,效率相当较高,但是如果order by 字段中所用的不是唯一字段,会造成无法选择出所要求的记录。因为只选择出了主键在要求记录范围内的记录。而且所有语句基本上都是top子句。适用于大量数据,并且是根本唯一值字段排序的地方。如果有重复值,但是重复值的个数少于每一页的记录数,则也可以正常获取数据。
第四种.插入临时表方法。
主要实现为
第一步根据排序建立临时表。
临时表插入排序后选择数据的rowId.
然后用联合的方法来实现排序。用到上面第三种方法。
该方法是通过一个只有主键及排序字段的临时表来进行分页,一般要求要建立存储过程。该方法最致命的问题是,当数据量较大时,要求建立一个根原表同样多条记录的主键,rowid,排序字段临时表,造成效率低下。一般不采用。
第五种.Sql server2005中ROW_NUMBER.来进行分页.
ROW_NUMBER
返回结果集分区内行的序列号,每个分区的第一行从 1 开始。
以下示例将返回行号为50到60(含)的行,并以OrderDate排序。
USE AdventureWorks;
GO
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader ) SELECT * FROM OrderedOrders WHERE RowNumber between 50 and 60;
该方法,主要是sql server2005中独有的功能,不适合于sql server2000及Access数据库,对数据库进行分区操作效率也不高。
作者:熊祥众
日期:2007-02-19