[转]双TOP二分法生成分页SQL类（支持MSSQL、ACCESS）

本文转自：http://www.cnblogs.com/jitian/archive/2011/03/22/1991961.html

博客开张，先发以前的几个老物件儿，以前写下来的，现在发上来权当记录和分享。 这个类是用来生成分页SQL的，记得那时思考写一个只传一条查询语句就能生成分页SQL的方法， 然后发现了双TOP分页法，不过双TOP法在后半页就很慢，后来一个同学发过来一篇文章： 　　2分法-通用存储过程分页(top max模式)版本(性能相对之前的not in版本极大提高) 通过它，发现了还有二分法这东东，甚感神奇，不过它是用MAX的方式，遂改为双TOP法。

代码如下：

?

using System; using System.Collections.Generic; using System.Text;   /// <summary> /// 构造分页后的SQL语句 /// </summary> public static class PagingHelper {      /// <summary>      /// 获取分页SQL语句，排序字段需要构成唯一记录      /// </summary>      /// <param name="recordCount">记录总数</param>      /// <param name="pageSize">每页记录数</param>      /// <param name="pageNum">当前页数</param>      /// <param name="safeSql">SQL查询语句</param>      /// <param name="orderField">排序字段，多个则用“,”隔开</param>      /// <returns>分页SQL语句</returns>      public static string CreatePagingSql( int recordCount, int pageSize, int pageNum, 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 (pageNum < 1)          {              pageNum = 1;          }          else if (pageNum > pageCount)          {              pageNum = pageCount;          }            StringBuilder sbSql = new StringBuilder();          //第一页时，直接使用TOP n，而不进行分页查询          if (pageNum == 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 (pageNum == pageCount)          {              sbSql.Append( " SELECT * FROM " );              sbSql.Append( " ( " );              sbSql.AppendFormat( " SELECT TOP {0} * " , recordCount - pageSize * (pageNum - 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 (pageNum < (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 * pageNum);              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 * (pageCount - 1)) + pageSize * (pageCount - pageNum)));              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();      }        /// <summary>      /// 获取记录总数SQL语句      /// </summary>      /// <param name="n">限定记录数</param>      /// <param name="safeSql">SQL查询语句</param>      /// <returns>记录总数SQL语句</returns>      public static string CreateTopnSql( int n, string safeSql)      {          return string .Format( " SELECT TOP {0} * FROM ({1}) AS T " , n, safeSql);      }        /// <summary>      /// 获取记录总数SQL语句      /// </summary>      /// <param name="safeSql">SQL查询语句</param>      /// <returns>记录总数SQL语句</returns>      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及以上版本已经提供了ROW_NUMBER()来进行分页处理，但是使用SQL2K进行开发的还是较多的。