ACCESS大数据高效分页语句

oracle的分页查询可以利用rowid伪列。

db2的分页查询可以利用row_number() over()聚合函数。

mysql有limit。

access仿佛先天缺陷，仅提供了top n。那如何利用top来实现分页查询呢？



假设在access中有表t1

createtable t1(

        tc1 varchar(50)notnullprimarykey,

        tc2 varchar(30),

        tc3 varchar(30)

)

随机插入20条数据。如果以每页5条来显示数据，如果要显示11至15条如何显示？

利用top n功能，前11条可以用以下sql完成。select top 11 tc1,tc2,tc3 from t1

同样前15条也可以这样：select top 15 tc1,tc2,tc3 from t1

想要得到11条到15条，估计一般都会想到差集，但access没提供差集except,可以利用notin实现。

select top 15 tc1,tc2,tc3 from t1 where tc1 notin(select top 10 tc1 from t1)

如果是一个比较大的表，用notin不能利用索引，使和效率极其低下，又该如何呢？



可以利用左连接来解决问题

select a.*from(select top15 tc1,tc2,tc3 from t1) a leftjoin(select top 10 tc1,tc2,tc3 from t1 ) b on a.tc1=b.tc1 where iif(b.tc1,'0','1')='1'

这种sql的好处是显而易见的，他有效的利用了表的主键索引。当然，由于access不能这样判断b.tc1 isnull,所以要改用iif(b.tc1,'0','1')='1'来曲线救国





C#DBHelper实现方式如下

///

/// 分页查询数据并返回DataTable的公共方法

///

/// 表名
/// 需要查询的字段
/// 每页显示数据的条数
/// 排除的数据量
/// where条件
/// 排序名称
/// 排序方式
///
publicstatic DataTable GetTable(String tableName, String field,int pageSize,int start, String sqlWhere, String sortName, String sortOrder,String primaryKey,out Int32 total)

{

        //String sql = String.Format("select top {0} {1} from {2} where {7} and {6} not in (select top {3} {6} from {2} where {7} order by {4} {5}) order by {4} {5} ",

        //        pageSize, field, tableName, start, sortName, sortOrder, primaryKey, sqlWhere);

        /*上面的分页效率极低，5000条数据几乎就不动了*/

        String sql = String.Format("select a.* from ( select top {1} * from {2} where {7} order by {3} {4}) a left join ( select top {5} * from {2} where {7} order by {3} {4}) b on a.{6}=b.{6} where iif(b.{6},'0','1')='1'",

              field, start + pageSize, tableName, sortName, sortOrder, start, primaryKey, sqlWhere);

        if(start <=0)

        {

              sql = String.Format("select top {0} {1} from {2} where {3} order by {4} {5} ",

              pageSize, field, tableName, sqlWhere, sortName, sortOrder);

        }

        DataTable dt = GetTable(sql, CommandType.Text,null);

      

        sql ="select count(1) from "+tableName+" where "+ sqlWhere;

        total = Convert.ToInt32(AccessHelper.ExecuteScalar(sql, CommandType.Text,null));

      

        return dt;

}


除了流行的top分页法之外，其实还有一种更简单，更容易理解，并且效率也不低的分页法。

先来一段传统的top分页法。

SELECT TOP 10 *
FROM TestTable
WHERE (ID >=
(SELECT MAX(id)
FROM (SELECT TOP 21 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID

原理：

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >=
(SELECT MAX(id)
FROM (SELECT TOP (页大小*(页数-1)+1) id
FROM 表
ORDER BY id) AS T))
ORDER BY ID

这种分页法效率不错，不过组合查询语句比较麻烦，尤其是当附带条件多的时候，看得人头脑发晕。参考：Sql分页的三种方法。其中的第一种采用Not In方式，效率很差，坚决反对使用。第三种方法，使用游标存储过程，在Access数据库中不通用。

下面介绍一种更简单的分页法

select * from news where nid between
(SELECT min(nid) from
(select top 4 nid from newsdata order by nid desc))
and
(SELECT min(nid) from
(select top 1 nid from newsdata order by nid desc))
order by nid desc

利用top和min函数分别找出分页的起始ID和结束ID，如果要按照升序排列，就要用top和max来找出起始ID和结束ID，之后在使用between语句直接选取。注意三个地方的排序方式必须一致，查询条件也必须一致。