自從用上了Orm，分頁這種事就是腰不酸腿不痛了。不過有時候想用純粹的ado.net來操作，希望返回的數據是原生的DataTable或DbDataReader類似的東西，故研究下怎么生成分頁的SQL語句。
平時接觸的數據庫有sql2000-2008，Oracle，SQLite 。 分頁邏輯，Oracle和SQLite相對好寫，就SQL事多，Sql2000下只能用top，排序2次，而Sql2005+就可以使用ROW_NUMBER()分析函數了，據說Sql2012對分頁又有了改進，暫時用不上那么高的版本，所以沒做。先看看目前這4種數據庫的分頁寫法:
-- 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] 區間的數據，在Sql語句中，盡可能在子查詢中減少查詢的結果集行數，然后針對排序過后的行號，在外層查詢中做條件篩選。 如Oracle寫法中 子查詢有ROWNUM <= 3010 ,Sql2005 中有 top 3010 * 。
當然今天要討論的問題，不是分頁語句的性能問題，如果你知道更好更快的寫法，歡迎交流。
上面的分頁寫法，基于的查詢sql語句是:
select * from User_Tables order by id desc
首先要從Sql語句中分析出行為，我把該Sql拆成了n部分，然后完成了以上拼接功能。按照模子往里面套數據，難度不大。
我們來描述另外一種場景，剛剛演示的sql是查詢 滿足條件下行數在[3001,3010]之間的數據，如果說總行數僅僅只有3500行，那么結果則是需要查詢出3010行數據，并取出最后10條，而前面3000條數據，是沒用的。
所以借鑒以前的經驗，姑且叫它 逆序分頁 。在知道總行數的前提下，我們可以進行分析，是否需要逆序分頁，因為逆序分頁得到分頁Sql語句，也是需要時間的，并非所有的情況都有必要這么做。之前有假設，數據僅僅有3500行，我們期望取出 按照id 倒敘排序后的[3001,3010]數據，換種方式理解，若按照id升序，我們期望取出的數據則是[491,500] 這個區間，然后將這個數據，再按照id倒敘排序，也就是我們需要的數據了。
理論知識差不多就說完了，需要了解更多的話，百度一下，你就知道。下面是代碼，有點長，展開當心:
public enum DBType { SqlServer2000, SqlServer, Oracle, SQLite } public class Page { /// <summary> /// 數據庫類別 /// </summary> public DBType dbType = DBType.Oracle; /// <summary> /// 逆序分頁行數，總行數大于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(); //統計的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) { //如果總行數不多或分頁的條數位于前半部分，沒必要逆序分頁 if (total < 100 || start <= total / 2) { return; } //sql正則分析過后的數組有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); } } //如果未定義排序規則，則無需做逆序分頁計算 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 * 逆序調整后 * 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
新聞熱點
疑難解答
