欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

仿orm自動生成分頁SQL分享

 更新時間:2014年01月09日 14:40:20   作者:  
平時接觸的數(shù)據(jù)庫有sql2000-2008,Oracle,SQLite 。 分頁邏輯,Oracle和SQLite相對好寫,就SQL事多,Sql2000下只能用top,排序2次,而Sql2005+就可以使用ROW_NUMBER()分析函數(shù)了,據(jù)說Sql2012對分頁又有了改進

先看看目前這4種數(shù)據(jù)庫的分頁寫法:

復(fù)制代碼 代碼如下:

-- 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

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

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

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

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

復(fù)制代碼 代碼如下:

select * from User_Tables order by id desc

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

逆序分頁

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

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

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

復(fù)制代碼 代碼如下:

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

            //恢復(fù)排序
            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.我的應(yīng)用場景則是在dbhelp類,某個方法傳遞sql,start,limit參數(shù)即可對sql查詢出來的結(jié)果進行分頁。其中start:查詢結(jié)果的起始行號(不包括它),limit:需要取出的行數(shù)。如 start:0,limit:15 則是取出前15條數(shù)據(jù)。

相關(guān)文章

  • C#中dynamic關(guān)鍵字的正確用法(推薦)

    C#中dynamic關(guān)鍵字的正確用法(推薦)

    dynamic的出現(xiàn)讓C#具有了弱語言類型的特性。dynamic是FrameWork4.0的新特性。這篇文章主要介紹了C#中dynamic關(guān)鍵字的正確用法(推薦)的相關(guān)資料,需要的朋友可以參考下
    2016-11-11
  • C#通過委托調(diào)用Button單擊事件的方法

    C#通過委托調(diào)用Button單擊事件的方法

    本文給大家分享的是通過委托取消Button事件switch-case的方法,十分的簡單實用,有需要的小伙伴可以參考下。
    2015-05-05
  • C#的并發(fā)機制優(yōu)秀在哪你知道么

    C#的并發(fā)機制優(yōu)秀在哪你知道么

    這篇文章主要為大家詳細介紹了C#的并發(fā)機制,文中示例代碼介紹的非常詳細,具有一定的參考價值,感興趣的小伙伴們可以參考一下,希望能夠給你帶來幫助
    2022-02-02
  • c#版在pc端發(fā)起微信掃碼支付的實例

    c#版在pc端發(fā)起微信掃碼支付的實例

    本篇文章主要介紹了c#版在pc端發(fā)起微信掃碼支付的實例,具有一定的參考價值,有興趣的可以了解一下。
    2016-11-11
  • C#實現(xiàn)獲取文本文件的編碼的一個類(區(qū)分GB2312和UTF8)

    C#實現(xiàn)獲取文本文件的編碼的一個類(區(qū)分GB2312和UTF8)

    這篇文章主要介紹了C#實現(xiàn)獲取文本文件的編碼一個類,本文給出類可以自動區(qū)分GB2312和UTF8,并同時給出了使用方法,需要的朋友可以參考下
    2014-09-09
  • c# 實現(xiàn)發(fā)送郵件的功能

    c# 實現(xiàn)發(fā)送郵件的功能

    這篇文章主要介紹了c# 如何實現(xiàn)發(fā)送郵件的功能,文中示例代碼非常詳細,幫助大家更好的理解和學(xué)習(xí),感興趣的朋友可以了解下
    2020-07-07
  • DevExpress根據(jù)條件設(shè)置GridControl RepositoryItem是否可編輯

    DevExpress根據(jù)條件設(shè)置GridControl RepositoryItem是否可編輯

    這篇文章主要介紹了DevExpress根據(jù)條件設(shè)置GridControl RepositoryItem是否可編輯,需要的朋友可以參考下
    2014-08-08
  • C# List介紹及具體用法

    C# List介紹及具體用法

    這篇文章主要介紹了C# List介紹及具體用法,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2019-12-12
  • C#自動類型轉(zhuǎn)換與強制類型轉(zhuǎn)換的講解

    C#自動類型轉(zhuǎn)換與強制類型轉(zhuǎn)換的講解

    今天小編就為大家分享一篇關(guān)于C#自動類型轉(zhuǎn)換與強制類型轉(zhuǎn)換的講解,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧
    2019-01-01
  • C#漢字轉(zhuǎn)拼音實例(支持多音字)

    C#漢字轉(zhuǎn)拼音實例(支持多音字)

    幾年前就在網(wǎng)上看到過漢字轉(zhuǎn)拼音的程序,大都就是按漢字的編碼轉(zhuǎn)換,單字對應(yīng)的算法實現(xiàn)的。但是都有一個共同的缺點,不能支持多音字。本篇文章主要介紹了C#漢字轉(zhuǎn)拼音實例(支持多音字),有興趣的可以了解一下。
    2016-12-12

最新評論