/// <summary>构造分页SQL</summary>
/// <param name="sql">SQL语句</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <param name="keyColumn">唯一键。用于not in分页</param>
/// <returns>分页SQL</returns>
public override String PageSplit(String sql, Int32 startRowIndex, Int32 maximumRows, String keyColumn)
{
// 从第一行开始，不需要分页
if (startRowIndex <= 0 && maximumRows < 1)
{
return(sql);
}
if (startRowIndex > 0)
{
// 指定了起始行，并且是SQL2005及以上版本，使用MS SQL 2012特有的分页算法
if (IsSQL2012)
{
// var str = $"select * from spt_values where type = 'p' order by {keyColumn} offset {startRowIndex} rows fetch next {maximumRows} rows only";
//SelectBuilder builder = new SelectBuilder();
//builder.Parse(sql);
//return MSPageSplit.PageSplit_Sql2012(builder, startRowIndex, maximumRows);
// 从第一行开始，不需要分页
if (startRowIndex <= 0)
{
if (maximumRows < 1)
{
return(sql);
}
else
{
var sql_ = FormatSqlserver2012SQL(sql);
return($"{sql_} offset 1 rows fetch next {maximumRows} rows only ");
}
}
if (maximumRows < 1)
{
throw new NotSupportedException("不支持取第几条数据之后的所有数据！");
}
var sql__ = FormatSqlserver2012SQL(sql);
return($"{sql__} offset {startRowIndex} rows fetch next {maximumRows} rows only ");
}
// 指定了起始行，并且是SQL2005及以上版本，使用RowNumber算法
if (IsSQL2005)
{
//return PageSplitRowNumber(sql, startRowIndex, maximumRows, keyColumn);
SelectBuilder builder = new SelectBuilder();
builder.Parse(sql);
return(MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, IsSQL2005).ToString());
}
}
// 如果没有Order By，直接调用基类方法
// 先用字符串判断，命中率高，这样可以提高处理效率
if (!sql.Contains(" Order "))
{
if (!sql.ToLower().Contains(" order "))
{
return(base.PageSplit(sql, startRowIndex, maximumRows, keyColumn));
}
}
//// 使用正则进行严格判断。必须包含Order By，并且它右边没有右括号)，表明有order by，且不是子查询的，才需要特殊处理
//MatchCollection ms = Regex.Matches(sql, @"\border\s*by\b([^)]+)$", RegexOptions.Compiled | RegexOptions.IgnoreCase);
//if (ms == null || ms.Count < 1 || ms[0].Index < 1)
String sql2 = sql;
String orderBy = CheckOrderClause(ref sql2);
if (String.IsNullOrEmpty(orderBy))
{
return(base.PageSplit(sql, startRowIndex, maximumRows, keyColumn));
}
// 已确定该sql最外层含有order by，再检查最外层是否有top。因为没有top的order by是不允许作为子查询的
if (Regex.IsMatch(sql, @"^[^(]+\btop\b", RegexOptions.Compiled | RegexOptions.IgnoreCase))
{
return(base.PageSplit(sql, startRowIndex, maximumRows, keyColumn));
}
//String orderBy = sql.Substring(ms[0].Index);
// 从第一行开始，不需要分页
if (startRowIndex <= 0)
{
if (maximumRows < 1)
{
return(sql);
}
else
{
return(String.Format("Select Top {0} * From {1} {2}", maximumRows, CheckSimpleSQL(sql2), orderBy));
}
//return String.Format("Select Top {0} * From {1} {2}", maximumRows, CheckSimpleSQL(sql.Substring(0, ms[0].Index)), orderBy);
}
#region Max/Min分页
// 如果要使用max/min分页法，首先keyColumn必须有asc或者desc
String kc = keyColumn.ToLower();
if (kc.EndsWith(" desc") || kc.EndsWith(" asc") || kc.EndsWith(" unknown"))
{
String str = PageSplitMaxMin(sql, startRowIndex, maximumRows, keyColumn);
if (!String.IsNullOrEmpty(str))
{
return(str);
}
keyColumn = keyColumn.Substring(0, keyColumn.IndexOf(" "));
}
#endregion
sql = CheckSimpleSQL(sql2);
if (String.IsNullOrEmpty(keyColumn))
{
throw new ArgumentNullException("keyColumn", "分页要求指定主键列或者排序字段！");
}
if (maximumRows < 1)
{
sql = String.Format("Select * From {1} Where {2} Not In(Select Top {0} {2} From {1} {3}) {3}", startRowIndex, sql, keyColumn, orderBy);
}
else
{
sql = String.Format("Select Top {0} * From {1} Where {2} Not In(Select Top {3} {2} From {1} {4}) {4}", maximumRows, sql, keyColumn, startRowIndex, orderBy);
}
return(sql);
}
public override SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
return(MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, false, b => CreateSession().QueryCount(b)));
}
public override SelectBuilder PageSplit(SelectBuilder builder, int startRowIndex, int maximumRows)
{
return(MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, IsSQL2005, b => CreateSession().QueryCount(b)));
}