Access大数据高效分页语句

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实现方式如下

/// <summary>

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

/// </summary>

/// <param name="tableName">表名</param>

/// <param name="field">需要查询的字段</param>

/// <param name="pageSize">每页显示数据的条数</param>

/// <param name="start">排除的数据量</param>

/// <param name="sqlWhere">where条件</param>

/// <param name="sortName">排序名称</param>

/// <param name="sortOrder">排序方式</param>

/// <returns></returns>

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;

}