先看看目前这4种数据库的分页写法:
-- Oracle
SELECT * FROM (
SELECT ROWNUM RN, PageTab.* FROM
(
SELECT * FROM User_Tables order by id desc
) PageTab where ROWNUM = 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 ///
/// 匹配SQL语句中Order By字段
///
private Regex rxOrderBy = new Regex(@"b(?ORDERs+BYs+(?:((?>((?)|)(?)|.?)*(?(depth)(?!)))|[w().])+)(?:s+(?ASC|DESC))?(?:s*,s*(?:((?>((?)|)(?)|.?)*(?(depth)(?!)))|[w().])+(?:s+(?:ASC|DESC))?)*", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
///
/// 匹配SQL语句中Distinct
///
private Regex rxDistinct = new Regex(@"ADISTINCTs", 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 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 " 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;
}
///
/// 生成逆序分页Sql语句
///
///
///
///
///
///
public void CreatePageSqlReverse(string sql,ref string[] sqls, int start, int limit, int total = 0)
{
//如果总行数不多或分页的条数位于前半部分，没必要逆序分页
if (total < 100 || start = 9980+1 and RN = 10+1 and RN 1)
{
pageSql.Append("Where RN >= :PageStart ");
}
}
else if (dbType == DBType.Oracle)
{
pageSql.AppendFormat("SELECT ROWNUM RN, PageTab.* FROM ( Select {0} {1} ) PageTab where ROWNUM = :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();
}
///
/// 生成常规Sql语句
///
///
///
///
///
///
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 = :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();
}
}