ACCESS[转]双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进展开发的要么较多的。

 

相关文章