分頁的寫法

　　自從用上了Orm，分頁這種事就是腰不酸腿不痛了。不過有時候想用純粹的ado.net來操作，希望返回的數(shù)據(jù)是原生的DataTable或DbDataReader類似的東西，故研究下怎么生成分頁的SQL語句。

　　平時接觸的數(shù)據(jù)庫有sql2000-2008，Oracle，SQLite 。分頁邏輯，Oracle和SQLite相對好寫，就SQL事多，Sql2000下只能用top，排序2次，而Sql2005+就可以使用ROW_NUMBER()分析函數(shù)了，據(jù)說Sql2012對分頁又有了改進，暫時用不上那么高的版本，所以沒做。先看看目前這4種數(shù)據(jù)庫的分頁寫法:

-- OracleSELECT * FROM (     SELECT ROWNUM RN,  PageTab.* FROM                  (                 SELECT * FROM User_Tables order by id desc                 ) PageTab  where ROWNUM <= 3010             ) Where RN>= 3001             -- SQLite    select * from User_Tables order by id desc limit 3001,10-- SQL2000SELECT TOP 100 PERCENT  * FROM (    SELECT TOP 10 * FROM (         SELECT TOP 3010 * from User_Tables  order by id desc ) PageTab order by id ASC  ) PageTab2 order by id desc-- SQL2005+    Select PageTab.* from (      Select top 3010 ROW_NUMBER() over (order by id desc) RN , * from User_Tables  ) PageTab Where RN >= 3001

　　其中針對 Oracle和Sql2005+的分頁寫法做個說明。

　　Oracle使用ROWNUM要比Row_Number()要快。sql示例中均是查詢 [3001,3010] 區(qū)間的數(shù)據(jù)，在Sql語句中，盡可能在子查詢中減少查詢的結果集行數(shù)，然后針對排序過后的行號，在外層查詢中做條件篩選。如Oracle寫法中子查詢有ROWNUM <= 3010 ,Sql2005 中有 top 3010 * 。

　　當然今天要討論的問題，不是分頁語句的性能問題，如果你知道更好更快的寫法，歡迎交流。

　　上面的分頁寫法，基于的查詢sql語句是:

select * from User_Tables order by id desc

　　首先要從Sql語句中分析出行為，我把該Sql拆成了n部分，然后完成了以上拼接功能。按照模子往里面套數(shù)據(jù)，難度不大。

逆序分頁

　　我們來描述另外一種場景，剛剛演示的sql是查詢滿足條件下行數(shù)在[3001,3010]之間的數(shù)據(jù)，如果說總行數(shù)僅僅只有3500行，那么結果則是需要查詢出3010行數(shù)據(jù)，并取出最后10條，而前面3000條數(shù)據(jù)，是沒用的。

　　所以借鑒以前的經(jīng)驗，姑且叫它 逆序分頁 。在知道總行數(shù)的前提下，我們可以進行分析，是否需要逆序分頁，因為逆序分頁得到分頁Sql語句，也是需要時間的，并非所有的情況都有必要這么做。之前有假設，數(shù)據(jù)僅僅有3500行，我們期望取出按照id 倒敘排序后的[3001,3010]數(shù)據(jù)，換種方式理解，若按照id升序，我們期望取出的數(shù)據(jù)則是[491,500] 這個區(qū)間，然后將這個數(shù)據(jù)，再按照id倒敘排序，也就是我們需要的數(shù)據(jù)了。

　　理論知識差不多就說完了，需要了解更多的話，百度一下，你就知道。下面是代碼，有點長，展開當心:

    public enum DBType    {        SqlServer2000,        SqlServer,        Oracle,        SQLite    }    public class Page    {        /// <summary>        /// 數(shù)據(jù)庫類別        /// </summary>        public DBType dbType = DBType.Oracle;        /// <summary>        /// 逆序分頁行數(shù)，總行數(shù)大于MaxRow，則會生成逆序分頁SQL        /// </summary>        public int MaxRow = 1000;//臨時測試，把值弄小點        /// <summary>        /// 匹配SQL語句中Select字段        /// </summary>        private Regex rxColumns = new Regex(@"\A\s*SELECT\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bFROM\b", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);        /// <summary>        /// 匹配SQL語句中Order By字段        /// </summary>        private Regex rxOrderBy = new Regex(@"\b(?<ordersql>ORDER\s+BY\s+(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+)(?:\s+(?<order>ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?)*", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);        /// <summary>        /// 匹配SQL語句中Distinct        /// </summary>        private Regex rxDistinct = new Regex(@"\ADISTINCT\s", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);        private string[] SplitSqlForPaging(string sql)        {            /*存儲分析過的SQL信息 依次為:             * 0.countsql             * 1.pageSql(保留位置此處不做分析)             * 2.移除了select的sql             * 3.order by 字段 desc             * 4.order by 字段             * 5.desc             */            var sqlInfo = new string[6];            // Extract the columns from "SELECT <whatever> FROM"            var m = rxColumns.Match(sql);            if (!m.Success)                return null;            // Save column list and replace with COUNT(*)            Group g = m.Groups[1];            sqlInfo[2] = sql.Substring(g.Index);            if (rxDistinct.IsMatch(sqlInfo[2]))                sqlInfo[0] = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);            else                sqlInfo[0] = sql.Substring(0, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length);            // Look for an "ORDER BY <whatever>" clause            m = rxOrderBy.Match(sqlInfo[0]);            if (!m.Success)            {                sqlInfo[3] = null;            }            else            {                g = m.Groups[0];                sqlInfo[3] = g.ToString();                //統(tǒng)計的SQL 移除order                sqlInfo[0] = sqlInfo[0].Substring(0, g.Index) + sqlInfo[0].Substring(g.Index + g.Length);                //存儲排序信息                sqlInfo[4] = m.Groups["ordersql"].Value;//order by xxx                sqlInfo[5] = m.Groups["order"].Value;//desc                 //select部分 移除order                sqlInfo[2] = sqlInfo[2].Replace(sqlInfo[3], string.Empty);            }            return sqlInfo;        }        /// <summary>        /// 生成逆序分頁Sql語句        /// </summary>        /// <param name="sql"></param>        /// <param name="sqls"></param>        /// <param name="start"></param>        /// <param name="limit"></param>        /// <param name="total"></param>        public void CreatePageSqlReverse(string sql,ref string[] sqls, int start, int limit, int total = 0)        {            //如果總行數(shù)不多或分頁的條數(shù)位于前半部分，沒必要逆序分頁            if (total < 100 || start <= total / 2)            {                return;            }            //sql正則分析過后的數(shù)組有5個值，若未分析，此處分析            if (sqls == null || sqls.Length == 6)            {                sqls = SplitSqlForPaging(sql);                if (sqls == null)                {                    //無法解析的SQL語句                    throw new Exception("can't parse sql to pagesql ,the sql is " + sql);                }            }            //如果未定義排序規(guī)則，則無需做逆序分頁計算            if (string.IsNullOrEmpty(sqls[5]))            {                return;            }            //逆序分頁檢查            string sqlOrder = sqls[3];            int end = start + limit;            //獲取逆序排序的sql            string sqlOrderChange = string.Compare(sqls[5], "desc", true) == 0 ?                string.Format("{0} ASC ", sqls[4]) :                string.Format("{0} DESC ", sqls[4]);            /*理論             * total:10000 start:9980 limit:10              * 則 end:9990 分頁條件為 RN >= 9980+1 and RN <= 9990             * 逆序調(diào)整后              * start = total - start = 20             * end = total - end = 10             * 交換start和end，分頁條件為 RN >= 10+1 and RN<= 20             */            //重新計算start和end            start = total - start;            end = total - end;            //交換start end            start = start + end;            end = start - end;            start = start - end;            //定義分頁SQL            var pageSql = new StringBuilder();            if (dbType == DBType.SqlServer2000)            {                pageSql.AppendFormat("SELECT TOP @PageLimit * FROM ( SELECT TOP @PageEnd {0} {1} ) ", sqls[2], sqlOrderChange);            }            else if (dbType == DBType.SqlServer)            {                //組織分頁SQL語句                pageSql.AppendFormat("SELECT PageTab.* FROM ( SELECT TOP @PageEnd ROW_NUMBER() over ({0}) RN , {1}  ) PageTab ",                    sqlOrderChange,                    sqls[2]);                //如果查詢不是第一頁，則需要判斷起始行號                if (start > 1)                {                    pageSql.Append("Where RN >= :PageStart ");                }            }            else if (dbType == DBType.Oracle)            {                pageSql.AppendFormat("SELECT ROWNUM RN,  PageTab.* FROM  ( Select {0} {1} ) PageTab  where ROWNUM <= :PageEnd ", sqls[2], sqlOrderChange);                //如果查詢不是第一頁，則需要判斷起始行號                if (start > 1)                {                    pageSql.Insert(0, "SELECT * FROM ( ");                    pageSql.Append(" ) ");                    pageSql.Append(" WHERE RN>= :PageStart ");                }            }            else if (dbType == DBType.SQLite)            {                pageSql.AppendFormat("SELECT * FROM ( SELECT {0} {1} limit  @PageStart,@PageLimit ) PageTab ", sqls[2], sqlOrderChange);            }            //恢復排序            pageSql.Append(sqlOrder);            //存儲生成的分頁SQL語句              sqls[1] = pageSql.ToString();            //臨時測試            sqls[1] = sqls[1].Replace("@", "").Replace(":", "").Replace("PageStart", ++start + "").Replace("PageEnd", end + "").Replace("PageLimit", limit + "");            Console.WriteLine("【count】{0}", sqls[0]);            Console.WriteLine("【page】{0}", sqls[1]);            Console.WriteLine();        }        /// <summary>        /// 生成常規(guī)Sql語句        /// </summary>        /// <param name="sql"></param>        /// <param name="sqls"></param>        /// <param name="start"></param>        /// <param name="limit"></param>        /// <param name="createCount"></param>        public void CreatePageSql(string sql, out string[] sqls, int start, int limit, bool createCount = false)        {            //需要輸出的sql數(shù)組            sqls = null;            //生成count的SQL語句 SqlServer生成分頁，必須通過正則拆分            if (createCount || dbType == DBType.SqlServer || dbType == DBType.SqlServer2000)            {                sqls = SplitSqlForPaging(sql);                if (sqls == null)                {                    //無法解析的SQL語句                    throw new Exception("can't parse sql to pagesql ,the sql is " + sql);                }            }            else            {                sqls = new string[2];            }            //組織分頁SQL語句            var pageSql = new StringBuilder();            var end = start + limit;            if (dbType == DBType.SqlServer2000)            {                pageSql.AppendFormat("SELECT TOP @PageEnd {0} {1}", sqls[2], sqls[3]);                if (start > 1)                {                    var orderChange = string.IsNullOrEmpty(sqls[5]) ? null :                        string.Compare(sqls[5], "desc", true) == 0 ?                        string.Format("{0} ASC ", sqls[4]) :                        string.Format("{0} DESC ", sqls[4]);                    pageSql.Insert(0, "SELECT TOP 100 PERCENT  * FROM (SELECT TOP @PageLimit * FROM ( ");                    pageSql.AppendFormat(" ) PageTab {0} ) PageTab2 {1}", orderChange, sqls[3]);                }            }            else if (dbType == DBType.SqlServer)            {                pageSql.AppendFormat(" Select top @PageEnd ROW_NUMBER() over ({0}) RN , {1}",                    string.IsNullOrEmpty(sqls[3]) ? "ORDER BY (SELECT NULL)" : sqls[3],                    sqls[2]);                //如果查詢不是第一頁，則需要判斷起始行號                if (start > 1)                {                    pageSql.Insert(0, "Select PageTab.* from ( ");                    pageSql.Append(" ) PageTab Where RN >= @PageStart");                }            }            else if (dbType == DBType.Oracle)            {                pageSql.Append("select ROWNUM RN,  PageTab.* from ");                pageSql.AppendFormat(" ( {0} ) PageTab ", sql);                pageSql.Append(" where ROWNUM <= :PageEnd ");                //如果查詢不是第一頁，則需要判斷起始行號                if (start > 1)                {                    pageSql.Insert(0, "select * from ( ");                    pageSql.Append(" ) Where RN>= :PageStart ");                }            }            else if (dbType == DBType.SQLite)            {                pageSql.AppendFormat("{0} limit @PageStart,@PageLimit", sql, start, limit);            }            //存儲生成的分頁SQL語句              sqls[1] = pageSql.ToString();            //臨時測試            sqls[1] = sqls[1].Replace("@", "").Replace(":", "").Replace("PageStart", ++start + "").Replace("PageEnd", end + "").Replace("PageLimit", limit + "");            Console.WriteLine("【count】{0}", sqls[0]);            Console.WriteLine("【page】{0}", sqls[1]);            Console.WriteLine();        }    }

其他

　　1.交換2個整數(shù)用了這樣的算法。交換a和b，a=a+b;b=a-b;b=a-b;這是原來找工作的時候被考到的，如果在不使用第三方變量的情況下交換2個整數(shù)。

　　2.Sql2000下由于是使用top進行分頁，除非條件一條數(shù)據(jù)都查不到，否則在分頁start和limit參數(shù)超過了總行數(shù)時，也會查詢出數(shù)據(jù)。

　　3.拆分Sql語句，參考了PetaPoco的部分源代碼。

　　4.我的應用場景則是在dbhelp類，某個方法傳遞sql，start，limit參數(shù)即可對sql查詢出來的結果進行分頁。其中start：查詢結果的起始行號(不包括它)，limit：需要取出的行數(shù)。如 start:0,limit:15 則是取出前15條數(shù)據(jù)。

測試代碼下載

本站僅提供存儲服務，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權內(nèi)容，請點擊舉報。

精品伊人久久大香线蕉,开心久久婷婷综合中文字幕,杏田冲梨,人妻无码aⅴ不卡中文字幕

分頁的寫法

逆序分頁

其他