/// <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 (count <= startRowIndex) { return(null); } // 刚好相等的就不必处理了 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排序 var orderby = builder.OrderBy ?? builder.KeyOrder; var keys = SelectBuilder.Split(orderby, out var isdescs); // 把排序反过来 var isdescs2 = new Boolean[keys.Length]; for (var i = 0; i < keys.Length; i++) { if (isdescs != null && isdescs.Length > i) { isdescs2[i] = !isdescs[i]; } else { isdescs2[i] = true; } } var reversekeyorder = SelectBuilder.Join(keys, isdescs2); // 构建Select Top 20 * From Table Order By ID Asc var builder1 = builder.Clone().AppendColumn(keys).Top(startRowIndex + maximumRows); // 必须加一个排序,否则会被优化掉而导致出错 if (String.IsNullOrEmpty(builder1.OrderBy)) { builder1.OrderBy = builder1.KeyOrder; } var builder2 = builder1.AsChild("XCode_T0", true).Top(maximumRows); // 要反向排序 builder2.OrderBy = reversekeyorder; var builder3 = builder2.AsChild("XCode_T1", true); // 结果列处理 builder3.Column = builder.Column; // 如果结果列包含有“.”,即有形如tab1.id、tab2.name之类的列时设为获取子查询的全部列 if ((!string.IsNullOrEmpty(builder3.Column)) && builder3.Column.Contains(".")) { builder3.Column = "*"; } // 让结果正向排序 builder3.OrderBy = orderby; return(builder3); }