数据库分页技术

 

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