/// <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; }