DataGrid连接Access的快速分页法（2）——SQL语句的选用（升序与降序）

作者：黎波

一、相关概念

     在 ACCESS 数据库中，一个表的主键（PRIMARY KEY，又称主索引）上必然建立了唯一索引（UNIQUE INDEX），因此主键字段的值是不会重复的。并且索引页依据索引列的值进行排序，每个索引记录包含一个指向它所引用的数据行的指针。我们可以利用主键这两个特点来实现对某条记录的定位，从而快速地取出某个分页上要显示的记录。

 

     举个例子，假设主键字段为 INTEGER 型，数据库表中的记录已经按主键字段的值升序排好，那么主键字段的值为“11”的记录肯定刚好在值为“12”的记录前面（假设数据库表中存在主键的值为“12”的记录）。如果主键字段不具备 UNIQUE 约束，数据库表中将有可能存在两个或两个以上主键字段的值为“11”的记录，这样就无法确定这些记录之间的前后位置了。

 

     下面就让我们看看如何利用主键来进行数据的分段查询吧。

二、升序

（1） @PageIndex <= @FirstIndex

 

     取第一页的数据是再简单不过了，我们只要用TOP @PageSize 就可以取出第一页要显示的记录。因为数据表中的记录已经按主键字段的值升序排好，所以省去了 ORDER BY 子句，速度更快。

 

SELECT TOP @PageSize @QueryFields FROM @TableName WHERE @Condition -- ORDER BY @PrimaryKey ASC

 

如图： @PageIndex = 0

 

 

（2） @FirstIndex < @PageIndex <= @MiddleIndex

 

     把取数据表前半部分记录和取后半部分记录的 SQL 语句分开写，可以有效地改善性能。后面我再详细解释这个问题。现在看看取前半部分记录的 SQL 语句。先取出当前页之前的所有记录的主键值，再从中选出最大值，然后取出主键值大于该最大值的前 @PageSize 条记录。值得注意的是，这里省去了两个 ORDER BY @PrimaryKey ASC 语句，分别在最里面和最外面的 SELECT 语句。前面已经说过，数据库表中的记录已经按主键字段的值升序排好，所以我们没有必要画蛇添足。

 

SELECT TOP @PageSize @QueryFields FROM @TableName WHERE @PrimaryKey > (      SELECT MAX ( @PrimaryKey ) FROM (           SELECT TOP @PageSize * @PageIndex @PrimaryKey           FROM @TableName           WHERE @Condition           -- ORDER BY @PrimaryKey ASC       ) TableA ) WHERE @Condition -- ORDER BY @PrimaryKey ASC

 

例如： @PageIndex = 1 ，红 --> 黄 --> 蓝

 

 

（3） @MiddleIndex < @PageIndex < @LastIndex

 

     接下来看看取数据表后半部分记录的 SQL 语句。该语句跟前面的语句算法的原理是一样的，只是方法稍微不同。

 

     先取出当前页之后的所有记录的主键值，再从中选出最小值，然后取出主键值小于该最小值的前 @PageSize 条记录。

 

SELECT * FROM (      SELECT TOP @PageSize @QueryFields      FROM @TableName      WHERE @PrimaryKey < (           SELECT MIN ( @PrimaryKey ) FROM (                SELECT TOP ( @RecordCount - @PageSize *( @PageIndex + 1 )) @PrimaryKey                FROM @TableName                WHERE @Condition                ORDER BY @PrimaryKey DESC           ) TableA      ) WHERE @Condition      ORDER BY @PrimaryKey DESC ) TableB ORDER BY @PrimaryKey ASC

 

     之所以把取数据表前半部分记录和取后半部分记录的 SQL 语句分开写，是因为使用取前半部分记录的 SQL 语句时，当前页前面的记录数目随页数递增，而我们还要从这些记录中取出它们的主键字段的值再从中选出最大值。这样一来，分页速度将随着页数的增加而减慢。因此我没有这样做，而是在当前页索引大于中间页索引时（ @MiddleIndex < @PageIndex ）选用了分页速度随着页数的增加而加快的算法。由此可见，假设把所有分页面划分为前面、中间和后面三部分，则最前面和最后面的分页速度最快，最中间的分页速度最慢。

 

例如： @PageIndex = 3 ，红 --> 黄 --> 蓝

 

 

（4） @PageIndex >= @LastIndex

 

     取最后一页的记录时要先计算出该页的记录数，作为 TOP 语句的条件，而不能直接用 TOP @PageSize ，这样取出来的记录并不只是最后一页的。其实很多网站确实这样做。

 

SELECT * FROM (      SELECT TOP ( @RecordCount - @PageSize * @LastIndex ) @QueryFields      FROM @TableName      WHERE @Condition      ORDER BY @PrimaryKey DESC ) TableA ORDER BY @PrimaryKey ASC

 

例如： @PageIndex = 4

三、降序

（1） @PageIndex <= @FirstIndex

SELECT TOP @PageSize @QueryFields FROM @TableName WHERE @Condition ORDER BY @PrimaryKey DESC

 

（2） @FirstIndex < @PageIndex <= @MiddleIndex

 

SELECT TOP @PageSize @QueryFields FROM @TableName WHERE @PrimaryKey < (     SELECT MIN ( @PrimaryKey ) FROM (         SELECT TOP @PageSize * @PageIndex @PrimaryKey         FROM @TableName         WHERE @Condition         ORDER BY @PrimaryKey DESC     ) TableA ) WHERE @Condition ORDER BY @PrimaryKey DESC

 

 

（3） @MiddleIndex < @PageIndex < @LastIndex

 

SELECT * FROM (     SELECT TOP @PageSize @QueryFields     FROM @TableName     WHERE @PrimaryKey > (         SELECT MAX ( @PrimaryKey ) FROM (             SELECT TOP ( @RecordCount - @PageSize *( @PageIndex + 1 )) @PrimaryKey             FROM @TableName             WHERE @Condition             -- ORDER BY @PrimaryKey ASC         ) TableA     ) WHERE @Condition     -- ORDER BY @PrimaryKey ASC ) TableB ORDER BY @PrimaryKey DESC

 

 

（4） @PageIndex >= @LastIndex

 

SELECT * FROM (     SELECT TOP ( @RecordCount - @PageSize * @LastIndex ) @QueryFields     FROM @TableName     WHERE @Condition     ORDER BY @PrimaryKey ASC ) TableA ORDER BY @PrimaryKey DESC

四、总结

       通过上面的讨论，相信大家应该看到了该分页方法的优势所在。在下一篇中，我将给大家一个动态生成上面 SQL 语句的类。

转载于:https://www.cnblogs.com/leonardleonard/archive/2004/12/23/1928481.html