SqlServer，ACCESS最快的分页函数

1. ///?<summary>
2. ????///?对有集聚索引的表（例如表具有唯一值的主健）获取分页的SQL
3. ????///?</summary>
4. ????///?<param?name="tableName">表名称</param>
5. ????///?<param?name="selectFields">要选择的列表字段，用逗号分隔</param>
6. ????///?<param?name="pkName">主键字段</param>
7. ????///?<param?name="condition">条件</param>
8. ????///?<param?name="isDESC">是否倒排</param>
9. ????///?<param?name="pageSize">页大小</param>
10. ????///?<param?name="pageNumber">页码</param>
11. ????///?<param?name="allCount">总记录数</param>
12. ????///?<returns></returns>
13. ????public?static?string?GetSqlPageByPK(string?tableName,string?selectFields,string?pkName,string?condition,bool?isDESC,int?pageSize,int?pageNumber,int?allCount)
14. ????{
15. ????????string?strSqlTemp?=?"";
16. ????????if?(isDESC)
17. ????????{
18. ????????????if?(pageNumber?<=?1)
19. ????????????????strSqlTemp?=?"SELECT?TOP?@Size?@Fields?FROM?@Table?WHERE?1=1?@Condition?ORDER?BY?@PK?DESC";
20. ????????????else
21. ????????????????strSqlTemp?=?@"
22. SELECT?TOP?@Size?@Fields
23. FROM?@Table
24. WHERE?@PK?<
25. (SELECT?MIN(@PK)?FROM
26. (SELECT?TOP?@CurrCount?@PK?FROM?@Table?WHERE?1=1?@Condition?ORDER?BY?@PK?DESC)?as?T
27. )
28. ORDER?BY?@PK?DESC
29. ";
30. ????????}
32. ????????else
33. ????????{
34. ????????????if?(pageNumber?<=?1)
35. ????????????????strSqlTemp?=?"SELECT?TOP?@Size?@Fields?FROM?@Table?WHERE?1=1?@Condition?ORDER?BY?@PK?";
36. ????????????else
37. ????????????????strSqlTemp?=?@"
38. SELECT?TOP?@Size?@Fields
39. FROM?@Table
40. WHERE?@PK?>
41. (SELECT?MAX(@PK)?FROM
42. (SELECT?TOP?@CurrCount?@PK?FROM?@Table?WHERE?1=1?@Condition?ORDER?BY?@PK?)?as?T
43. )
44. ORDER?BY?@PK?
45. ";
46. ????????}
48. ????????//
49. ????????int?iCurrCount?=?(pageNumber?-?1)?*?pageSize;
50. ????????if?(iCurrCount?+?pageSize?>?allCount)
51. ????????????pageSize?=?allCount?-?iCurrCount;
53. ????????string?SQL?=?strSqlTemp.Replace("@Size",?pageSize.ToString?())
54. ????????????.Replace("@Fields",?selectFields)
55. ????????????.Replace("@Table",?tableName)
56. ????????????.Replace("@PK",?pkName)
57. ????????????.Replace("@Condition",condition);
58. ????????if(pageNumber>1)
59. ????????????SQL?=?SQL.Replace("@CurrCount",?iCurrCount.ToString?());
61. ????????return?SQL;
62. ????}

?* 最快速的 分页 函数

适用于有唯一值 的集聚索引的快速分页，可以适用于千万级别的分页

算法如下：

--最快的分页算法 ，使用于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

注：需要计算最末页的数量

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!