/// <summary>
/// 格式化SQL SERVER 2012分页前半部分SQL语句
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private String FormatSqlserver2012SQL(String sql)
{
SelectBuilder builder = new SelectBuilder();
builder.Parse(sql);
var sb = new StringBuilder();
sb.Append("Select ");
sb.Append(builder.ColumnOrDefault);
sb.Append(" From ");
sb.Append(builder.Table);
if (!String.IsNullOrEmpty(builder.Where))
{
sb.Append(" Where type='p' and " + builder.Where);
}
else
{
sb.Append(" Where type='p' ");
}
if (!String.IsNullOrEmpty(builder.GroupBy))
{
sb.Append(" Group By " + builder.GroupBy);
}
if (!String.IsNullOrEmpty(builder.Having))
{
sb.Append(" Having " + builder.Having);
}
if (!String.IsNullOrEmpty(builder.OrderBy))
{
sb.Append(" Order By " + builder.OrderBy);
}
return(sb.ToString());
}
/// <summary>构造分页SQL</summary>
/// <remarks>
/// 两个构造分页SQL的方法，区别就在于查询生成器能够构造出来更好的分页语句，尽可能的避免子查询。
/// MS体系的分页精髓就在于唯一键，当唯一键带有Asc/Desc/Unkown等排序结尾时，就采用最大最小值分页，否则使用较次的TopNotIn分页。
/// TopNotIn分页和MaxMin分页的弊端就在于无法完美的支持GroupBy查询分页，只能查到第一页，往后分页就不行了，因为没有主键。
/// </remarks>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
public virtual SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
// 从第一行开始，不需要分页
if (startRowIndex <= 0 && maximumRows < 1)
{
return(builder);
}
var sql = PageSplit(builder.ToString(), startRowIndex, maximumRows, builder.Key);
var sb = new SelectBuilder();
sb.Parse(sql);
return(sb);
}
/// <summary>根据SQL创建，带缓存</summary>
/// <remarks>
/// 对于非常复杂的查询语句，正则平衡的处理器消耗很大
/// </remarks>
/// <param name="sql"></param>
/// <returns></returns>
public static SelectBuilder Create(String sql)
{
var key = $"SelectBuilder#{sql}";
var sb = Store.Get <SelectBuilder>(key);
if (sb != null)
{
return(sb);
}
sb = new SelectBuilder();
sb.Parse(sql);
Store.Set(key, sb, 10 * 60);
return(sb);
}
/// <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);
}
/// <summary>构造分页SQL</summary>
/// <remarks>
/// 两个构造分页SQL的方法，区别就在于查询生成器能够构造出来更好的分页语句，尽可能的避免子查询。
/// MS体系的分页精髓就在于唯一键，当唯一键带有Asc/Desc/Unkown等排序结尾时，就采用最大最小值分页，否则使用较次的TopNotIn分页。
/// TopNotIn分页和MaxMin分页的弊端就在于无法完美的支持GroupBy查询分页，只能查到第一页，往后分页就不行了，因为没有主键。
/// </remarks>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
public virtual SelectBuilder PageSplit(SelectBuilder builder, Int32 startRowIndex, Int32 maximumRows)
{
// 从第一行开始，不需要分页
if (startRowIndex <= 0 && maximumRows < 1) return builder;
var sql = PageSplit(builder.ToString(), startRowIndex, maximumRows, builder.Key);
var sb = new SelectBuilder();
sb.Parse(sql);
return sb;
}
/// <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;
// 指定了起始行，并且是SQL2005及以上版本，使用RowNumber算法
if (startRowIndex > 0 && 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;
}
