仿orm自动生成分页SQL分享-木庄网络博客

本文整理自网络，侵删。

先看看目前这4种数据库的分页写法:

代码如下:

-- 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] 区间的数据，在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();

阅读剩余部分