仿orm自動(dòng)生成分頁(yè)SQL分享
先看看目前這4種數(shù)據(jù)庫(kù)的分頁(yè)寫(xiě)法:
-- Oracle
SELECT * 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
-- 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
其中針對(duì) Oracle和Sql2005+的分頁(yè)寫(xiě)法做個(gè)說(shuō)明。
Oracle使用ROWNUM要比Row_Number()要快。sql示例中均是查詢(xún) [3001,3010] 區(qū)間的數(shù)據(jù)，在Sql語(yǔ)句中，盡可能在子查詢(xún)中減少查詢(xún)的結(jié)果集行數(shù)，然后針對(duì)排序過(guò)后的行號(hào)，在外層查詢(xún)中做條件篩選。 如Oracle寫(xiě)法中 子查詢(xún)有ROWNUM <= 3010 ,Sql2005 中有 top 3010 * 。
當(dāng)然今天要討論的問(wèn)題，不是分頁(yè)語(yǔ)句的性能問(wèn)題，如果你知道更好更快的寫(xiě)法，歡迎交流。
上面的分頁(yè)寫(xiě)法，基于的查詢(xún)sql語(yǔ)句是:
select * from User_Tables order by id desc
首先要從Sql語(yǔ)句中分析出行為，我把該Sql拆成了n部分，然后完成了以上拼接功能。按照模子往里面套數(shù)據(jù)，難度不大。
逆序分頁(yè)
我們來(lái)描述另外一種場(chǎng)景，剛剛演示的sql是查詢(xún) 滿(mǎn)足條件下行數(shù)在[3001,3010]之間的數(shù)據(jù)，如果說(shuō)總行數(shù)僅僅只有3500行，那么結(jié)果則是需要查詢(xún)出3010行數(shù)據(jù)，并取出最后10條，而前面3000條數(shù)據(jù)，是沒(méi)用的。
所以借鑒以前的經(jīng)驗(yàn)，姑且叫它 逆序分頁(yè) 。在知道總行數(shù)的前提下，我們可以進(jìn)行分析，是否需要逆序分頁(yè)，因?yàn)槟嫘蚍猪?yè)得到分頁(yè)Sql語(yǔ)句，也是需要時(shí)間的，并非所有的情況都有必要這么做。之前有假設(shè)，數(shù)據(jù)僅僅有3500行，我們期望取出 按照id 倒敘排序后的[3001,3010]數(shù)據(jù)，換種方式理解，若按照id升序，我們期望取出的數(shù)據(jù)則是[491,500] 這個(gè)區(qū)間，然后將這個(gè)數(shù)據(jù)，再按照id倒敘排序，也就是我們需要的數(shù)據(jù)了。
理論知識(shí)差不多就說(shuō)完了，需要了解更多的話，百度一下，你就知道。下面是代碼，有點(diǎn)長(zhǎng)，展開(kāi)當(dāng)心:
public enum DBType
{
SqlServer2000,
SqlServer,
Oracle,
SQLite
}
public class Page
{
/// <summary>
/// 數(shù)據(jù)庫(kù)類(lèi)別
/// </summary>
public DBType dbType = DBType.Oracle;
/// <summary>
/// 逆序分頁(yè)行數(shù)，總行數(shù)大于MaxRow，則會(huì)生成逆序分頁(yè)SQL
/// </summary>
public int MaxRow = 1000;//臨時(shí)測(cè)試，把值弄小點(diǎn)
/// <summary>
/// 匹配SQL語(yǔ)句中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語(yǔ)句中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語(yǔ)句中Distinct
/// </summary>
private Regex rxDistinct = new Regex(@"\ADISTINCT\s", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
private string[] SplitSqlForPaging(string sql)
{
/*存儲(chǔ)分析過(guò)的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)計(jì)的SQL 移除order
sqlInfo[0] = sqlInfo[0].Substring(0, g.Index) + sqlInfo[0].Substring(g.Index + g.Length);
//存儲(chǔ)排序信息
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>
/// 生成逆序分頁(yè)Sql語(yǔ)句
/// </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ù)不多或分頁(yè)的條數(shù)位于前半部分，沒(méi)必要逆序分頁(yè)
if (total < 100 || start <= total / 2)
{
return;
}
//sql正則分析過(guò)后的數(shù)組有5個(gè)值，若未分析，此處分析
if (sqls == null || sqls.Length == 6)
{
sqls = SplitSqlForPaging(sql);
if (sqls == null)
{
//無(wú)法解析的SQL語(yǔ)句
throw new Exception("can't parse sql to pagesql ,the sql is " + sql);
}
}
//如果未定義排序規(guī)則，則無(wú)需做逆序分頁(yè)計(jì)算
if (string.IsNullOrEmpty(sqls[5]))
{
return;
}
//逆序分頁(yè)檢查
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 分頁(yè)條件為 RN >= 9980+1 and RN <= 9990
* 逆序調(diào)整后
* start = total - start = 20
* end = total - end = 10
* 交換start和end，分頁(yè)條件為 RN >= 10+1 and RN<= 20
*/
//重新計(jì)算start和end
start = total - start;
end = total - end;
//交換start end
start = start + end;
end = start - end;
start = start - end;
//定義分頁(yè)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)
{
//組織分頁(yè)SQL語(yǔ)句
pageSql.AppendFormat("SELECT PageTab.* FROM ( SELECT TOP @PageEnd ROW_NUMBER() over ({0}) RN , {1} ) PageTab ",
sqlOrderChange,
sqls[2]);
//如果查詢(xún)不是第一頁(yè)，則需要判斷起始行號(hào)
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);
//如果查詢(xún)不是第一頁(yè)，則需要判斷起始行號(hào)
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);
}
//恢復(fù)排序
pageSql.Append(sqlOrder);
//存儲(chǔ)生成的分頁(yè)SQL語(yǔ)句
sqls[1] = pageSql.ToString();
//臨時(shí)測(cè)試
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語(yǔ)句
/// </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語(yǔ)句 SqlServer生成分頁(yè)，必須通過(guò)正則拆分
if (createCount || dbType == DBType.SqlServer || dbType == DBType.SqlServer2000)
{
sqls = SplitSqlForPaging(sql);
if (sqls == null)
{
//無(wú)法解析的SQL語(yǔ)句
throw new Exception("can't parse sql to pagesql ,the sql is " + sql);
}
}
else
{
sqls = new string[2];
}
//組織分頁(yè)SQL語(yǔ)句
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]);
//如果查詢(xún)不是第一頁(yè)，則需要判斷起始行號(hào)
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 ");
//如果查詢(xún)不是第一頁(yè)，則需要判斷起始行號(hào)
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);
}
//存儲(chǔ)生成的分頁(yè)SQL語(yǔ)句
sqls[1] = pageSql.ToString();
//臨時(shí)測(cè)試
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個(gè)整數(shù)用了這樣的算法。交換a和b，a=a+b;b=a-b;b=a-b;這是原來(lái)找工作的時(shí)候被考到的，如果在不使用第三方變量的情況下交換2個(gè)整數(shù)。
2.Sql2000下由于是使用top進(jìn)行分頁(yè)，除非條件一條數(shù)據(jù)都查不到，否則在分頁(yè)start和limit參數(shù)超過(guò)了總行數(shù)時(shí)，也會(huì)查詢(xún)出數(shù)據(jù)。
3.拆分Sql語(yǔ)句，參考了PetaPoco的部分源代碼。
4.我的應(yīng)用場(chǎng)景則是在dbhelp類(lèi)，某個(gè)方法傳遞sql，start，limit參數(shù)即可對(duì)sql查詢(xún)出來(lái)的結(jié)果進(jìn)行分頁(yè)。其中start：查詢(xún)結(jié)果的起始行號(hào)(不包括它)，limit：需要取出的行數(shù)。如 start:0,limit:15 則是取出前15條數(shù)據(jù)。
相關(guān)文章
dynamic的出現(xiàn)讓C#具有了弱語(yǔ)言類(lèi)型的特性。dynamic是FrameWork4.0的新特性。這篇文章主要介紹了C#中dynamic關(guān)鍵字的正確用法(推薦)的相關(guān)資料,需要的朋友可以參考下2016-11-11
C#通過(guò)委托調(diào)用Button單擊事件的方法
本文給大家分享的是通過(guò)委托取消Button事件switch-case的方法，十分的簡(jiǎn)單實(shí)用，有需要的小伙伴可以參考下。2015-05-05
這篇文章主要為大家詳細(xì)介紹了C#的并發(fā)機(jī)制，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來(lái)幫助2022-02-02
本篇文章主要介紹了c#版在pc端發(fā)起微信掃碼支付的實(shí)例，具有一定的參考價(jià)值，有興趣的可以了解一下。2016-11-11
C#實(shí)現(xiàn)獲取文本文件的編碼的一個(gè)類(lèi)（區(qū)分GB2312和UTF8）
這篇文章主要介紹了C#實(shí)現(xiàn)獲取文本文件的編碼一個(gè)類(lèi),本文給出類(lèi)可以自動(dòng)區(qū)分GB2312和UTF8,并同時(shí)給出了使用方法,需要的朋友可以參考下2014-09-09
這篇文章主要介紹了c# 如何實(shí)現(xiàn)發(fā)送郵件的功能，文中示例代碼非常詳細(xì)，幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下2020-07-07
DevExpress根據(jù)條件設(shè)置GridControl RepositoryItem是否可編輯
這篇文章主要介紹了DevExpress根據(jù)條件設(shè)置GridControl RepositoryItem是否可編輯,需要的朋友可以參考下2014-08-08
這篇文章主要介紹了C# List介紹及具體用法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-12-12
C#自動(dòng)類(lèi)型轉(zhuǎn)換與強(qiáng)制類(lèi)型轉(zhuǎn)換的講解
今天小編就為大家分享一篇關(guān)于C#自動(dòng)類(lèi)型轉(zhuǎn)換與強(qiáng)制類(lèi)型轉(zhuǎn)換的講解，小編覺(jué)得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來(lái)看看吧2019-01-01
幾年前就在網(wǎng)上看到過(guò)漢字轉(zhuǎn)拼音的程序，大都就是按漢字的編碼轉(zhuǎn)換，單字對(duì)應(yīng)的算法實(shí)現(xiàn)的。但是都有一個(gè)共同的缺點(diǎn)，不能支持多音字。本篇文章主要介紹了C#漢字轉(zhuǎn)拼音實(shí)例（支持多音字），有興趣的可以了解一下。2016-12-12