- /// <summary>
- /// 对有集聚索引的表(例如表具有唯一值的主健)获取分页的SQL
- /// </summary>
- /// <param name="tableName">表名称</param>
- /// <param name="selectFields">要选择的列表字段,用逗号分隔</param>
- /// <param name="pkName">主键字段</param>
- /// <param name="condition">条件</param>
- /// <param name="isDESC">是否倒排</param>
- /// <param name="pageSize">页大小</param>
- /// <param name="pageNumber">页码</param>
- /// <param name="allCount">总记录数</param>
- /// <returns></returns>
- public static string GetSqlPageByPK(string tableName,string selectFields,string pkName,string condition,bool isDESC,int pageSize,int pageNumber,int allCount)
- {
- string strSqlTemp = "";
- if (isDESC)
- {
- if (pageNumber <= 1)
- strSqlTemp = "SELECT TOP @Size @Fields FROM @Table WHERE 1=1 @Condition ORDER BY @PK DESC";
- else
- strSqlTemp = @"
- SELECT TOP @Size @Fields
- FROM @Table
- WHERE @PK <
- (SELECT MIN(@PK) FROM
- (SELECT TOP @CurrCount @PK FROM @Table WHERE 1=1 @Condition ORDER BY @PK DESC) as T
- )
- ORDER BY @PK DESC
- ";
- }
- else
- {
- if (pageNumber <= 1)
- strSqlTemp = "SELECT TOP @Size @Fields FROM @Table WHERE 1=1 @Condition ORDER BY @PK ";
- else
- strSqlTemp = @"
- SELECT TOP @Size @Fields
- FROM @Table
- WHERE @PK >
- (SELECT MAX(@PK) FROM
- (SELECT TOP @CurrCount @PK FROM @Table WHERE 1=1 @Condition ORDER BY @PK ) as T
- )
- ORDER BY @PK
- ";
- }
- //
- int iCurrCount = (pageNumber - 1) * pageSize;
- if (iCurrCount + pageSize > allCount)
- pageSize = allCount - iCurrCount;
- string SQL = strSqlTemp.Replace("@Size", pageSize.ToString ())
- .Replace("@Fields", selectFields)
- .Replace("@Table", tableName)
- .Replace("@PK", pkName)
- .Replace("@Condition",condition);
- if(pageNumber>1)
- SQL = SQL.Replace("@CurrCount", iCurrCount.ToString ());
- return SQL;
- }
* 最快速的 分页 函数
适用于有唯一值 的集聚索引的快速分页,可以适用于千万级别的分页
算法如下:
--最快的分页算法 ,使用于2-N 页 倒序,当前查询的是第 3 页
--10=(3-1)*2
select top 5 *
from TB
where id <
(select min(id) from
(select top 10 id from TB order by id desc) as T
)
order by id desc
---------------
--------------
--最快的分页算法 ,使用于2-N 页 升序,当前查询的是第 3 页
--10=(3-1)*2
select top 5 *
from TB
where id >
(select max(id) from
(select top 10 id from TB order by id asc) as T
)
order by id asc
注:需要计算最末页的数量