先看看目前這4種數據庫的分頁寫法:
-- SQL2000
SELECT 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語句是:
首先要從Sql語句中分析出行為，我把該Sql拆成了n部分，然后完成了以上拼接功能。按照模子往里面套數據，難度不大。
逆序分頁
我們來描述另外一種場景，剛剛演示的sql是查詢 滿足條件下行數在[3001,3010]之間的數據，如果說總行數僅僅只有3500行，那么結果則是需要查詢出3010行數據，并取出最后10條，而前面3000條數據，是沒用的。
所以借鑒以前的經驗，姑且叫它 逆序分頁 。在知道總行數的前提下，我們可以進行分析，是否需要逆序分頁，因為逆序分頁得到分頁Sql語句，也是需要時間的，并非所有的情況都有必要這么做。之前有假設，數據僅僅有3500行，我們期望取出 按照id 倒敘排序后的[3001,3010]數據，換種方式理解，若按照id升序，我們期望取出的數據則是[491,500] 這個區間，然后將這個數據，再按照id倒敘排序，也就是我們需要的數據了。
理論知識差不多就說完了，需要了解更多的話，百度一下，你就知道。下面是代碼，有點長，展開當心:
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 ( 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>
/// 生成常規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數組
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個整數用了這樣的算法。交換a和b，a=a+b;b=a-b;b=a-b;這是原來找工作的時候被考到的，如果在不使用第三方變量的情況下交換2個整數。
2.Sql2000下由于是使用top進行分頁，除非條件一條數據都查不到，否則在分頁start和limit參數超過了總行數時，也會查詢出數據。
3.拆分Sql語句，參考了PetaPoco的部分源代碼。
4.我的應用場景則是在dbhelp類，某個方法傳遞sql，start，limit參數即可對sql查詢出來的結果進行分頁。其中start：查詢結果的起始行號(不包括它)，limit：需要取出的行數。如 start:0,limit:15 則是取出前15條數據。
新聞熱點
疑難解答
