当前位置: 编程技术>.net/c#/asp.net
sql server与Access数据库使用top分页的方法浅析
来源: 互联网 发布时间:2014-08-30
本文导语: 在sql server与access中,双top法相比于not in和max法,其可以传入一条sql语句来生成分页sql语句,也可以实现多字段排序。 缺点: 要求排序字段必须构成唯一记录,且select后的字段列表中,不允许出现与排序字段同名的字段。 ...
在sql server与access中,双top法相比于not in和max法,其可以传入一条sql语句来生成分页sql语句,也可以实现多字段排序。
缺点:
要求排序字段必须构成唯一记录,且select后的字段列表中,不允许出现与排序字段同名的字段。
虽然sql server 2005及以上版本已提供了rownum()来进行分页处理。
通过一个例子,来体会下此种用法的利弊吧。
代码:
代码示例:
using system.collections.generic;
using system;
using system.text;
///
/// 构造分页后的sql语句
///
public static class paginghelper
{
///
/// 获取分页sql语句,排序字段需要构成唯一记录
///
/// 记录总数
/// 每页记录数
/// 当前页数
/// sql查询语句
/// 排序字段,多个则用“,”隔开
/// 分页sql语句
public static string createpagingsql(int _recordcount, int _pagesize, int _pageindex, string _safesql, string _orderfield)
{
//重新组合排序字段,防止有错误
string[] arrstrorders = _orderfield.split(new char[] { ',' }, stringsplitoptions.removeemptyentries);
stringbuilder sboriginalorder = new stringbuilder(); //原排序字段
stringbuilder sbreverseo教程rder = new stringbuilder(); //与原排序字段相反,用于分页
for (int i = 0; i < arrstrorders.length; i++)
{
arrstrorders[i] = arrstrorders[i].trim(); //去除前后空格
if (i != 0)
{
sboriginalorder.append(", ");
sbreverseorder.append(", ");
}
sboriginalorder.append(arrstrorders[i]);
int index = arrstrorders[i].indexof(" "); //判断是否有升降标识
if (index > 0)
{
//替换升降标识,分页所需
bool flag = arrstrorders[i].indexof(" desc", stringcomparison.ordinalignorecase) != -1;
sbreverseorder.appendformat("{0} {1}", arrstrorders[i].remove(index), flag ? "asc" : "desc");
} www.
else
{
sbreverseorder.appendformat("{0} desc", arrstrorders[i]);
}
}
//计算总页数
_pagesize = _pagesize == 0 ? _recordcount : _pagesize;
int pagecount = (_recordcount + _pagesize - 1) / _pagesize;
//检查当前页数
if (_pageindex < 1)
{
_pageindex = 1;
}
else if (_pageindex > pagecount)
{
_pageindex = pagecount;
}
stringbuilder sbsql = new stringbuilder();
//第一页时,直接使用top n,而不进行分页查询
if (_pageindex == 1)
{
sbsql.appendformat(" select top {0} * ", _pagesize);
sbsql.appendformat(" from ({0}) as t ", _safesql);
sbsql.appendformat(" order by {0} ", sboriginalorder.tostring());
}
//最后一页时,减少一个top n
else if (_pageindex == pagecount)
{
sbsql.append(" select * from ");
sbsql.append(" ( ");
sbsql.appendformat(" select top {0} * ", _recordcount - _pagesize * (_pageindex - 1));
sbsql.appendformat(" from ({0}) as t ", _safesql);
sbsql.appendformat(" order by {0} ", sbreverseorder.tostring());
sbsql.append(" ) as t ");
sbsql.appendformat(" order by {0} ", sboriginalorder.tostring());
}
//前半页数时的分页
else if (_pageindex < (pagecount / 2 + pagecount % 2))
{
sbsql.append(" select * from ");
sbsql.append(" ( ");
sbsql.appendformat(" select top {0} * from ", _pagesize);
sbsql.append(" ( ");
sbsql.appendformat(" select top {0} * ", _pagesize * _pageindex);
sbsql.appendformat(" from ({0}) as t ", _safesql);
sbsql.appendformat(" order by {0} ", sboriginalorder.tostring());
sbsql.append(" ) as t ");
sbsql.appendformat(" order by {0} ", sbreverseorder.tostring());
sbsql.append(" ) as t ");
sbsql.appendformat(" order by {0} ", sboriginalorder.tostring());
}
//后半页数时的分页
else
{
sbsql.appendformat(" select top {0} * from ", _pagesize);
sbsql.append(" ( ");
sbsql.appendformat(" select top {0} * ", ((_recordcount % _pagesize) + _pagesize * (pagecount - _pageindex)));
sbsql.appendformat(" from ({0}) as t ", _safesql);
sbsql.appendformat(" order by {0} ", sbreverseorder.tostring());
sbsql.append(" ) as t ");
sbsql.appendformat(" order by {0} ", sboriginalorder.tostring());
}
return sbsql.tostring();
}
///
/// 获取记录总数sql语句
///
/// 限定记录数
/// sql查询语句
/// 记录总数sql语句
public static string createtopnsql(int _n, string _safesql)
{
return string.format(" select top {0} * from ({1}) as t ", _n, _safesql);
}
///
/// 获取记录总数sql语句
///
/// sql查询语句
/// 记录总数sql语句
public static string createcountingsql(string _safesql)
{
return string.format(" select count(1) as recordcount from ({0}) as t ", _safesql);
}
}
using system;
using system.text;
///
/// 构造分页后的sql语句
///
public static class paginghelper
{
///
/// 获取分页sql语句,排序字段需要构成唯一记录
///
/// 记录总数
/// 每页记录数
/// 当前页数
/// sql查询语句
/// 排序字段,多个则用“,”隔开
/// 分页sql语句
public static string createpagingsql(int _recordcount, int _pagesize, int _pageindex, string _safesql, string _orderfield)
{
//重新组合排序字段,防止有错误
string[] arrstrorders = _orderfield.split(new char[] { ',' }, stringsplitoptions.removeemptyentries);
stringbuilder sboriginalorder = new stringbuilder(); //原排序字段
stringbuilder sbreverseo教程rder = new stringbuilder(); //与原排序字段相反,用于分页
for (int i = 0; i < arrstrorders.length; i++)
{
arrstrorders[i] = arrstrorders[i].trim(); //去除前后空格
if (i != 0)
{
sboriginalorder.append(", ");
sbreverseorder.append(", ");
}
sboriginalorder.append(arrstrorders[i]);
int index = arrstrorders[i].indexof(" "); //判断是否有升降标识
if (index > 0)
{
//替换升降标识,分页所需
bool flag = arrstrorders[i].indexof(" desc", stringcomparison.ordinalignorecase) != -1;
sbreverseorder.appendformat("{0} {1}", arrstrorders[i].remove(index), flag ? "asc" : "desc");
} www.
else
{
sbreverseorder.appendformat("{0} desc", arrstrorders[i]);
}
}
//计算总页数
_pagesize = _pagesize == 0 ? _recordcount : _pagesize;
int pagecount = (_recordcount + _pagesize - 1) / _pagesize;
//检查当前页数
if (_pageindex < 1)
{
_pageindex = 1;
}
else if (_pageindex > pagecount)
{
_pageindex = pagecount;
}
stringbuilder sbsql = new stringbuilder();
//第一页时,直接使用top n,而不进行分页查询
if (_pageindex == 1)
{
sbsql.appendformat(" select top {0} * ", _pagesize);
sbsql.appendformat(" from ({0}) as t ", _safesql);
sbsql.appendformat(" order by {0} ", sboriginalorder.tostring());
}
//最后一页时,减少一个top n
else if (_pageindex == pagecount)
{
sbsql.append(" select * from ");
sbsql.append(" ( ");
sbsql.appendformat(" select top {0} * ", _recordcount - _pagesize * (_pageindex - 1));
sbsql.appendformat(" from ({0}) as t ", _safesql);
sbsql.appendformat(" order by {0} ", sbreverseorder.tostring());
sbsql.append(" ) as t ");
sbsql.appendformat(" order by {0} ", sboriginalorder.tostring());
}
//前半页数时的分页
else if (_pageindex < (pagecount / 2 + pagecount % 2))
{
sbsql.append(" select * from ");
sbsql.append(" ( ");
sbsql.appendformat(" select top {0} * from ", _pagesize);
sbsql.append(" ( ");
sbsql.appendformat(" select top {0} * ", _pagesize * _pageindex);
sbsql.appendformat(" from ({0}) as t ", _safesql);
sbsql.appendformat(" order by {0} ", sboriginalorder.tostring());
sbsql.append(" ) as t ");
sbsql.appendformat(" order by {0} ", sbreverseorder.tostring());
sbsql.append(" ) as t ");
sbsql.appendformat(" order by {0} ", sboriginalorder.tostring());
}
//后半页数时的分页
else
{
sbsql.appendformat(" select top {0} * from ", _pagesize);
sbsql.append(" ( ");
sbsql.appendformat(" select top {0} * ", ((_recordcount % _pagesize) + _pagesize * (pagecount - _pageindex)));
sbsql.appendformat(" from ({0}) as t ", _safesql);
sbsql.appendformat(" order by {0} ", sbreverseorder.tostring());
sbsql.append(" ) as t ");
sbsql.appendformat(" order by {0} ", sboriginalorder.tostring());
}
return sbsql.tostring();
}
///
/// 获取记录总数sql语句
///
/// 限定记录数
/// sql查询语句
/// 记录总数sql语句
public static string createtopnsql(int _n, string _safesql)
{
return string.format(" select top {0} * from ({1}) as t ", _n, _safesql);
}
///
/// 获取记录总数sql语句
///
/// sql查询语句
/// 记录总数sql语句
public static string createcountingsql(string _safesql)
{
return string.format(" select count(1) as recordcount from ({0}) as t ", _safesql);
}
}