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;
}