/// <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>构造分页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) { /* * Oracle的rownum分页,在内层有Order By非主键排序时,外层的rownum会优先生效, * 导致排序字段有相同值时无法在多次查询中保持顺序,(Oracle算法参数会改变)。 * 其一,可以在排序字段后加上主键,确保排序内容唯一; * 其二,可以在第二层提前取得rownum,然后在第三层外使用; * * 原分页算法始于2005年,只有在特殊情况下遇到分页出现重复数据的BUG: * 排序、排序字段不包含主键且不唯一、排序字段拥有相同数值的数据行刚好被分到不同页上 */ // 从第一行开始,不需要分页 if (startRowIndex <= 0) { if (maximumRows <= 0) { return(builder); } // 如果带有排序,需要生成完整语句 if (builder.OrderBy.IsNullOrEmpty()) { return(builder.AsChild("T0", false).AppendWhereAnd("rownum<={0}", maximumRows)); } //if (maximumRows < 1) return builder.AsChild("XCode_T0", false).AppendWhereAnd("rownum>={0}", startRowIndex + 1); } builder = builder.AsChild("T0", false); //builder.AppendWhereAnd("rownum<={0}", startRowIndex + maximumRows); 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>最经典的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); }