/// <summary>RowNumber分页算法</summary>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns></returns>
static SelectBuilder RowNumber(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
//if (maximumRows < 1)
// sql = String.Format("Select * From (Select *, row_number() over({2}) as rowNumber From {1}) XCode_Temp_b Where rowNumber>={0}", startRowIndex + 1, sql, orderBy);
//else
// sql = String.Format("Select * From (Select *, row_number() over({3}) as rowNumber From {1}) XCode_Temp_b Where rowNumber Between {0} And {2}", startRowIndex + 1, sql, startRowIndex + maximumRows, orderBy);
// 如果包含分组，则必须作为子查询
SelectBuilder builder1 = builder.CloneWithGroupBy("XCode_T0");
builder1.Column = String.Format("{0}, row_number() over(Order By {1}) as rowNumber", builder.ColumnOrDefault, builder.OrderBy ?? builder.KeyOrder);
SelectBuilder builder2 = builder1.AsChild("XCode_T1");
// 结果列保持原样
builder2.Column = builder.Column;
// row_number()直接影响了排序，这里不再需要
builder2.OrderBy = null;
if (maximumRows < 1)
{
builder2.Where = String.Format("rowNumber>={0}", startRowIndex + 1);
}
else
{
builder2.Where = String.Format("rowNumber Between {0} And {1}", startRowIndex + 1, startRowIndex + maximumRows);
}
return(builder2);
}
/// <summary>按唯一数字最大最小分页，性能很好。必须指定一个数字型排序列。</summary>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
static SelectBuilder MaxMin(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
if (builder.Keys == null || builder.Keys.Length != 1)
{
throw new ArgumentNullException("Key", "TopNotIn分页算法要求指定单一主键列！" + builder.ToString());
}
// 分页标准 Select (20,10,ID Desc)
// Select Top 10 * From Table Where ID>(Select max(ID) From (Select Top 20 ID From Table Order By ID) Order By ID Desc) Order By ID Desc
SelectBuilder builder1 = builder.Clone().Top(startRowIndex, builder.Key);
SelectBuilder builder2 = builder1.AsChild("XCode_T0");
builder2.Column = String.Format("{0}({1})", builder.IsDesc ? "Min" : "Max", builder.Key);
SelectBuilder builder3 = null;
if (maximumRows < 1)
{
builder3 = builder.CloneWithGroupBy("XCode_T1");
}
else
{
builder3 = builder.Clone().Top(maximumRows);
}
// 如果本来有Where字句，加上And，当然，要区分情况按是否有必要加圆括号
builder3.AppendWhereAnd("{0}{1}({2})", builder.Key, builder.IsDesc ? "<" : ">", builder2.ToString());
return(builder3);
}
/// <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 override SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
/*
* DB2的rownum分页，在内层有Order By非主键排序时，外层的rownum会优先生效，
* 导致排序字段有相同值时无法在多次查询中保持顺序，（DB2算法参数会改变）。
* 其一，可以在排序字段后加上主键，确保排序内容唯一；
* 其二，可以在第二层提前取得rownum，然后在第三层外使用；
*
* 原分页算法始于2005年，只有在特殊情况下遇到分页出现重复数据的BUG：
* 排序、排序字段不包含主键且不唯一、排序字段拥有相同数值的数据行刚好被分到不同页上
*/
// 从第一行开始，不需要分页
if (startRowIndex <= 0)
{
if (maximumRows <= 0)
{
return(builder);
}
//// 如果带有排序，需要生成完整语句
//if (builder.OrderBy.IsNullOrEmpty())
return(builder.AsChild("T0", false).AppendWhereAnd("rownum<={0}", maximumRows));
}
else if (maximumRows < 1)
{
throw new NotSupportedException();
}
builder = builder.AsChild("T0", false).AppendWhereAnd("rownum<={0}", startRowIndex + maximumRows);
builder.Column = "T0.*, rownum as rowNumber";
builder = builder.AsChild("T1", false).AppendWhereAnd("rowNumber>{0}", startRowIndex);
//builder = builder.AsChild("T0", false);
//builder.Column = "T0.*, rownum as rowNumber";
//builder = builder.AsChild("T1", false);
//builder.AppendWhereAnd("rowNumber>{0}", startRowIndex);
//if (maximumRows > 0) builder.AppendWhereAnd("rowNumber<={0}", startRowIndex + maximumRows);
return(builder);
}
/// <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 override SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
// 从第一行开始，不需要分页
if (startRowIndex <= 0)
{
if (maximumRows > 0)
{
builder = builder.AsChild("XCode_T0", false).AppendWhereAnd("rownum<={0}", maximumRows);
}
return(builder);
}
if (maximumRows < 1)
{
return(builder.AsChild("XCode_T0", false).AppendWhereAnd("rownum>={0}", startRowIndex + 1));
}
builder = builder.AsChild("XCode_T0", false).AppendWhereAnd("rownum<={0}", startRowIndex + maximumRows);
builder.Column = "XCode_T0.*, rownum as rowNumber";
builder = builder.AsChild("XCode_T1", false).AppendWhereAnd("rowNumber>{0}", startRowIndex);
return(builder);
}
/// <summary>双Top分页，因为没有使用not in，性能比NotIn要好。语句必须有排序，不需额外指定排序列</summary>
/// <param name="builder"></param>
/// <param name="startRowIndex"></param>
/// <param name="maximumRows"></param>
/// <param name="queryCountCallback">查询总记录数的委托，近供DoubleTop使用</param>
/// <returns></returns>
static SelectBuilder DoubleTop(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows, Func <SelectBuilder, Int64> queryCountCallback)
{
if (builder.Keys == null)
{
throw new ArgumentNullException("Key", "DoubleTop分页算法要求指定排序列！" + builder.ToString());
}
// 采用DoubleTop分页，最后一页可能有问题，需要特殊处理
if (queryCountCallback != null)
{
// 查询总记录数，计算是否最后一页
var count = queryCountCallback(builder);
// 刚好相等的就不必处理了
if (startRowIndex + maximumRows > count)
{
maximumRows = count - startRowIndex;
}
}
// 分页标准 Select (20,10,ID Desc)
// 1，按原始排序取20+10行，此时目标页位于底部
// 2，倒序过来取10行，得到目标页，但是顺序是反的
// 3，再倒序一次
// 显然，原始语句必须有排序，否则无法倒序。另外，也不能处理maximumRows<1的情况
// Select * From (Select Top 10 * From (Select Top 20+10 * From Table Order By ID Desc) Order By ID Asc) Order By ID Desc
// 找到排序，优先采用排序字句来做双Top排序
String orderby = builder.OrderBy ?? builder.KeyOrder;
Boolean[] isdescs = null;
String[] keys = SelectBuilder.Split(orderby, out isdescs);
// 把排序反过来
Boolean[] isdescs2 = new Boolean[keys.Length];
for (int i = 0; i < keys.Length; i++)
{
if (isdescs != null && isdescs.Length > i)
{
isdescs2[i] = !isdescs[i];
}
else
{
isdescs2[i] = true;
}
}
String reversekeyorder = SelectBuilder.Join(keys, isdescs2);
// 构建Select Top 20 * From Table Order By ID Asc
SelectBuilder builder1 = builder.Clone().AppendColumn(keys).Top(startRowIndex + maximumRows);
// 必须加一个排序，否则会被优化掉而导致出错
if (String.IsNullOrEmpty(builder1.OrderBy))
{
builder1.OrderBy = builder1.KeyOrder;
}
SelectBuilder builder2 = builder1.AsChild("XCode_T0").Top(maximumRows);
// 要反向排序
builder2.OrderBy = reversekeyorder;
SelectBuilder builder3 = builder2.AsChild("XCode_T1");
// 结果列保持原样
builder3.Column = builder.Column;
// 让结果正向排序
builder3.OrderBy = orderby;
return(builder3);
}
