程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Access數據庫 >> 關於Access數據庫 >> mssql access top分頁方法

mssql access top分頁方法

編輯:關於Access數據庫

     mssql access top分頁方法

    雙top法相比於not in和max法,就是可以傳入一條sql語句來生成分頁sql語句,也可多字段排序;
    但是有利也有弊,它要求排序字段必須構成唯一記錄,且select後的字段列表中,不允許出現與排序字段同名的字段。
    雖然sql2k5及以上版本已經提供了rownum()來進行分頁處理,但是使用sql2k進行開發的還是較多的

    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="_pageindex">當前頁數</param>
        /// <param name="_safesql">sql查詢語句</param>
        /// <param name="_orderfield">排序字段,多個則用“,”隔開</param>
        /// <returns>分頁sql語句</returns>
        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");
                }
                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();
        }

        /// <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);
        }
    }

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved