/// <summary>最经典的NotIn分页，通用但是效率最差。只需指定一个排序列。</summary>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
static SelectBuilder TopNotIn(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)
// 1，取目标页之前的20行
// 2，排除前20行之后取10行
// Select Top 10 * From Table Where ID Not In(Select Top 20 ID From Table)
// 构建Select Top 20 ID From Table
var builder1 = builder.Clone().Top(startRowIndex, builder.Key);
SelectBuilder builder2 = null;
if (maximumRows < 1)
builder2 = builder.CloneWithGroupBy("XCode_T0");
else
builder2 = builder.Clone().Top(maximumRows);
builder2.AppendWhereAnd("{0} Not In({1})", builder.Key, builder1);
// 结果列处理
builder2.Column = builder.Column;
// 如果结果列包含有“.”，即有形如tab1.id、tab2.name之类的列时设为获取子查询的全部列
if ((!string.IsNullOrEmpty(builder2.Column)) && builder2.Column.Contains("."))
{
builder2.Column = "*";
}
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
var builder1 = builder.Clone().Top(startRowIndex, builder.Key);
var builder2 = builder1.AsChild("XCode_T0", true);
builder2.Column = String.Format("{0}({1})", builder.IsDesc ? "Min" : "Max", builder.Key);
SelectBuilder builder3 = null;
if (maximumRows < 1)
{
builder3 = builder.CloneWithGroupBy("XCode_T1", true);
}
else
{
builder3 = builder.Clone().Top(maximumRows);
}
// 如果本来有Where字句，加上And，当然，要区分情况按是否有必要加圆括号
builder3.AppendWhereAnd("{0}{1}({2})", builder.Key, builder.IsDesc ? "<" : ">", builder2);
return(builder3);
}
/// <summary>最经典的NotIn分页，通用但是效率最差。只需指定一个排序列。</summary>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
static SelectBuilder TopNotIn(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)
// 1，取目标页之前的20行
// 2，排除前20行之后取10行
// Select Top 10 * From Table Where ID Not In(Select Top 20 ID From Table)
// 构建Select Top 20 ID From Table
SelectBuilder builder1 = builder.Clone().Top(startRowIndex, builder.Key);
SelectBuilder builder2 = null;
if (maximumRows < 1)
{
builder2 = builder.CloneWithGroupBy("XCode_T0");
}
else
{
builder2 = builder.Clone().Top(maximumRows);
}
builder2.AppendWhereAnd("{0} Not In({1})", builder.Key, builder1.ToString());
return(builder2);
}
/// <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>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);
// 如果包含分组，则必须作为子查询
var builder1 = builder.CloneWithGroupBy("XCode_T0");
builder1.Column = String.Format("{0}, row_number() over(Order By {1}) as rowNumber", builder.ColumnOrDefault, builder.OrderBy ?? builder.KeyOrder);
var builder2 = builder1.AsChild("XCode_T1");
// 结果列处理
builder2.Column = builder.Column;
// 如果结果列包含有“.”，即有形如tab1.id、tab2.name之类的列时设为获取子查询的全部列
if ((!string.IsNullOrEmpty(builder2.Column)) && builder2.Column.Contains("."))
{
builder2.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);
}
public override SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
//return MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, IsSQL2005, b => CreateSession().QueryCount(b));
if (startRowIndex <= 0)
{
if (maximumRows < 1)
{
return(builder);
}
else if (builder.KeyIsOrderBy)
{
return(builder.Clone().Top(maximumRows));
}
}
if (builder.Keys == null || builder.Keys.Length < 1)
{
throw new XCodeException("分页算法要求指定排序列！" + builder.ToString());
}
// 如果包含分组，则必须作为子查询
var builder1 = builder.CloneWithGroupBy("XCode_T0", true);
//builder1.Column = String.Format("{0}, row_number() over(Order By {1}) as rowNumber", builder.ColumnOrDefault, builder.OrderBy ?? builder.KeyOrder);
// 不必追求极致，把所有列放出来
builder1.Column = "*, row_number() over(Order By {0}) as rowNumber".F(builder.OrderBy ?? builder.KeyOrder);
var builder2 = builder1.AsChild("XCode_T1", true);
// 结果列处理
//builder2.Column = builder.Column;
//// 如果结果列包含有“.”，即有形如tab1.id、tab2.name之类的列时设为获取子查询的全部列
//if ((!string.IsNullOrEmpty(builder2.Column)) && builder2.Column.Contains("."))
//{
// builder2.Column = "*";
//}
// 不必追求极致，把所有列放出来
builder2.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>最经典的NotIn分页，通用但是效率最差。只需指定一个排序列。</summary>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
static SelectBuilder TopNotIn(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)
// 1，取目标页之前的20行
// 2，排除前20行之后取10行
// Select Top 10 * From Table Where ID Not In(Select Top 20 ID From Table)
// 构建Select Top 20 ID From Table
SelectBuilder builder1 = builder.Clone().Top(startRowIndex, builder.Key);
SelectBuilder builder2 = null;
if (maximumRows < 1)
builder2 = builder.CloneWithGroupBy("XCode_T0");
else
builder2 = builder.Clone().Top(maximumRows);
builder2.AppendWhereAnd("{0} Not In({1})", builder.Key, builder1.ToString());
return builder2;
}
/// <summary>最经典的NotIn分页，通用但是效率最差。只需指定一个排序列。</summary>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行，0表示第一行</param>
/// <param name="maximumRows">最大返回行数，0表示所有行</param>
/// <returns>分页SQL</returns>
static SelectBuilder TopNotIn(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)
// 1，取目标页之前的20行
// 2，排除前20行之后取10行
// Select Top 10 * From Table Where ID Not In(Select Top 20 ID From Table)
// 构建Select Top 20 ID From Table
var builder1 = builder.Clone().Top(startRowIndex, builder.Key);
SelectBuilder builder2 = null;
if (maximumRows < 1)
{
builder2 = builder.CloneWithGroupBy("XCode_T0", true);
}
else
{
builder2 = builder.Clone().Top(maximumRows);
}
builder2.AppendWhereAnd("{0} Not In({1})", builder.Key, builder1);
// 结果列处理
builder2.Column = builder.Column;
// 如果结果列包含有“.”，即有形如tab1.id、tab2.name之类的列时设为获取子查询的全部列
if ((!string.IsNullOrEmpty(builder2.Column)) && builder2.Column.Contains("."))
{
builder2.Column = "*";
}
return(builder2);
}
/// <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);
// 如果包含分组，则必须作为子查询
var builder1 = builder.CloneWithGroupBy("XCode_T0");
//builder1.Column = String.Format("{0}, row_number() over(Order By {1}) as rowNumber", builder.ColumnOrDefault, builder.OrderBy ?? builder.KeyOrder);
// 不必追求极致，把所有列放出来
builder1.Column = "*, row_number() over(Order By {0}) as rowNumber".F(builder.OrderBy ?? builder.KeyOrder);
var builder2 = builder1.AsChild("XCode_T1");
// 结果列处理
//builder2.Column = builder.Column;
//// 如果结果列包含有“.”，即有形如tab1.id、tab2.name之类的列时设为获取子查询的全部列
//if ((!string.IsNullOrEmpty(builder2.Column)) && builder2.Column.Contains("."))
//{
// builder2.Column = "*";
//}
// 不必追求极致，把所有列放出来
builder2.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
var builder1 = builder.Clone().Top(startRowIndex, builder.Key);
var 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);
return builder3;
}
/// <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;
}
