双TOP二分法生成分页SQL类(支持MSSQL、ACCESS)
博客开张,先发以前的几个老物件儿,以前写下来的,现在发上来权当记录和分享。
这个类是用来生成分页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进行开发的还是较多的。