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