Mikel
[转载]双TOP二分法生成分页SQL类（支持MSSQL、ACCESS） – 祭天 – 博客园.
博客开张，先发以前的几个老物件儿，以前写下来的，现在发上来权当记录和分享。
这个类是用来生成分页SQL的，记得那时思考写一个只传一条查询语句就能生成分页SQL的方法，
然后发现了双TOP分页法，不过双TOP法在后半页就很慢，后来一个同学发过来的一篇文章：
2分法-通用存储过程分页(top max模式)版本(性能相对之前的not in版本极大提高)
通过它，发现了还二分法这东东，甚感神奇，不过它是用MAX的方式，逐改为双TOP法。
代码如下：
using System;
using System.Collections.Generic;
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 sbReverseOrder = 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");
}
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);
}
}
双TOP法相比于NOT IN和MAX法，就是可以传入一条SQL语句来生成分页SQL语句，也可多字段排序；
但是有利也有弊，它要求排序字段必须构成唯一记录，且SELECT后的字段列表中，不允许出现与排序字段同名的字段。
虽然SQL2K5及以上版本已经提供了ROWNUM()来进行分页处理，但是使用SQL2K进行开发的还是较多的。
