解析分页思想+分页实战

  • Z时代
  • 2024-01-10
  • 分类：综合

database

一、排除Top分页法（自命名，非规范）

思想：所谓“排除Top分页”，主要依靠“排除”和Top这个两大核心步骤。首先查询当前页码之前的数据，然后将该数据从总数据中排除掉，在从剩下的数据中获取前N条记录，就可以得到当前页码的数据。

举例-分页条件：每页显示2条记录，查看第3页

以SQLServer语法实现并说明步骤：

　步骤1：编写基础查询语句并按照字段字段进行排序，排序字段会作为后续的数据筛选条件

1select*from tableName orderby id

 

　步骤2：获取一个公式，该公式用于计算当前页码之前所有页面的总记录数

　　例子的当前页为第3页，那么在第3页之前有2页，并且每页显示2条，最后可得出之前页面总共为：2乘以2等于4条记录。然后根据逻辑得出计算公式：(当前页码-1)*每页显示条数，根据例子分页条件得出：(3-1)*2

 

　步骤3：使用步骤2的公式加到步骤1语句中作为Top的数值，从而可以查出当前页之前的所有数据。

1 　selecttop ((3-1)*2) *from tableName  orderby id

 

　步骤4：在总数据中排除掉“当前页之前的所有数据”，然后进行排序

1select*from tableName
2
3Where id notin ( Selecttop  ((3-1)*2) * id fromorderby id )
4
5Orderby id

 

　步骤5：在步骤4的语句中，取出前N条（每页显示的条数）

1selecttop2*from tableName
3Where id notin ( selecttop  ((3-1)*2) * id fromorderby id )
5Orderby id

 

SQLite数据库写法：

1select*from tableName where id
2notin (select did from DishInfo orderby did limit (3-1)*2 )  
3orderby did limit 2

 注意：SQLite数据库中没有Top关键字，而使用limit关键字来取前N条。

 

Oracle数据库写法：

1Select*from
2Select*from tableName where id notin (
3Select id from (Seletc id from tableName where rownum<=(3-1)*2orderby id ) 
4 ) orderby id ) where rownum<=2

 注意：Oracle数据库中没有Top关键字，而使用rownum来取前N条。

 

二、游标偏移法（自命名，非规范）

思想和游标的概念比较相似，主要用于SQLite数据库结合 Limit和 Offset关键字来实现。

举例-分页条件：每页显示2条记录，查看第4页

分页思想：

1.求出偏移的起始点，也就从上一页的最后一条数据开始向下偏移。

得出偏移起始点计算公式为：(当前页-1)*每页显示条数

数据例图：

2.获取偏移的数量，也就是等于每页显示的条数，根据示例分页条件得出偏移设立为2。

3.根据偏移起始点和偏移数量得出指定页的数据。

数据例图：

注意：偏移到过的所有行数据即为当前页数据，不包含偏移起始点所在行。

 根据思想编写的SQL：

1select*from tableName orderby id Limit 2 OFFSET (4-1)*2

 

OFFSET ：指定偏移起始点，也就是上一页的最后一行

Limit ：表示偏移数量

该思想和SubString字符串截取的方法思想也比较像，OFFSET相当于定义截取的位置，Limit 相当于截取的数量。

 

 

三、指定范围区间获取法（自命名，非规范）

思想：为查询指定的一个唯一连续性的标识，并结合分页条件计算出筛选范围，对标识进行筛选从而得到当前页的数据。

例-分页条件：每页显示2条记录，查看第3页的数据

以SQLServer语法实现如下：

步骤：

　　1.使用row_number函数为查询数据的每行生成一个唯一连续性的标识，该标识可以用于作为筛选范围的条件字段

1Select* ,rn=row_number()  over (orderby id) from tableName

　　2.获取筛选数据的起始位置，该起始位置就是当前页之前所有页的总条数，例如当前页是第3页之前就是有2页，每页显示2条记录，那么当前页之前所有页的总条数为：(3-1)*2，因为要显示第3页的第一条，则在计算公式上在+1，最后得到的公式为：(当前页-1)*每页显示的条数+1。

 

　　3.获取筛选数据的结束位置，该位置就是当前页和之前所有页的所有记录数，这也相当于是当前页的最后一条。例如当前页是第3页，结合每页显示2条记录，那么当前页和之前所有页的所有记录数为：3*2，公式为：当前页*每页显示条数。

 

　　4.将步骤1的语句进行嵌套查询，之所以嵌套是因为row_number生成的字段必须嵌套后才能作为条件进行筛选。然后将步骤2和步骤3获取的起始位置和结束位置作为范围条件。根据示例的分页条件最终的语句如下：

1Select*from (
2
3Select* ,rn=row_number() over (orderby id) from tableName
4
5 ) tempwhere rn between (3-1)*2+1and3*2

 

Oracle数据库实现

例-分页条件：每页显示2条记录，查看第3页的数据

Oracle和SQLServer的思想是一致的，主要是在步骤1的时候，oracle生成唯一标识之前需要先进行排序，然后在将查询进行嵌套在外层生成行的唯一标识。

1Select*from (
2
3Select t.*,rownum as rn  (select*from tableName orderby id) t
4
5 ) where rn between (3-1)*2+1and3*2

 

 

四、存储过程动态分页

SQLServer存储过程参考代码：

 1--动态数据分页存储过程
 2createproc sp_DataPaging
 3@pageIndexint, --当前页码
 4@pageSizeint , --每页显示条数
 5@tableNamevarchar(200),
 6@orderByFieldvarchar(20), --排序字段
 7@rowCountint output ,--总记录数
 8@pageCountint output --总页数
 9as
10begin
11
12declare@tempSqlnvarchar (max),@beginIndexint ,@endIndexint
13
14--1.求总记录数
15set@tempSql="set @rowCount= (select count(*) from "+@tableName+")"
16execute sp_executesql @tempSql, N"@rowCount int output",@rowCount output
17
18/*
19*2.求总页数=总记录数除以每页显示条数
20*乘以1.0是为了让结果为小数，从而通过CEILING将小数向上取整，解决最后一页不满足显示条数的情况
21*/
22set@pageCount=CEILING(@rowCount*1.0/@pageSize*1.0)
23
24--3.求出筛选范围
25set@beginIndex=(@pageIndex-1)*@pageSize+1
26set@endIndex=@pageIndex*@pageSize
27
28--4.生成查询当前页数据的语句
29set@tempSql=" select * from (
30select  *,rd=ROW_NUMBER() over (order by "+@orderByField+")  from "+@tableName+"
31) temp  where rd between "+cast(@beginIndexasvarchar)+" and "+cast(@endIndexasvarchar) +""
32
33--5.执行语句查询当前页数据
34exec (@tempSql)
35end
36go
37
38--测试 
39select*from Students orderby StudentId 
40declare@pageIndexint, @pageSizeint , @rownumint   ,@pagenumint ,@tableNamevarchar(200),@orderByFieldvarchar(20) 
41begin
42set@pageIndex=5
43set@pageSize=5
44set@tableName="Students"
45set@orderByField="studentid"
46end
47exec sp_DataPaging @pageIndex,@pageSize,@tableName,@orderByField,@rownum output,@pagenum output
48go

 

Oracle存储过程参考代码：

 1CREATEORREPLACEPROCEDURE sp_DataPaging
 2 (
 3  tableName INVARCHAR, --表名
 4  orderByFiled INVARCHAR, --排序字段
 5  pageIndex INNUMBER, --当前页码
 6  pageSize INNUMBER,  --每页显示条数
 7 rowtotal OUT NUMBER  , --总条数
 8 pageCount OUT NUMBER,  --总页数
 9 P_CUR_OUT    OUT SYS_REFCURSOR
10)
11AS
12   tempSql Varchar2(800);
13   beginIndex NUMBER;
14   endIndex NUMBER;
15BEGIN
16
17--1.求总条数
18 tempSql:="select count(*) from "||tableName;
19EXECUTE IMMEDIATE tempSql INTO rowtotal;
20
21--2.求总页数
22 pageCount:=CEIL(rowtotal/pageSize);
23
24--3.计算筛选数据的起始和结束范围
25 beginIndex:=(pageIndex-1)*pageSize+1;
26 endIndex:=pageIndex*pageSize;
27
28--4.生成查询当前分页的语句
29 tempSql:="SELECT * FROM  (
30SELECT t.*,ROWNUM AS rn FROM  (SELECT *  FROM "||tableName||" ORDER BY "||orderByFiled||" ) t
31) WHERE rn BETWEEN "||beginIndex||" AND    "||endIndex;
32
33--5.执行查询
34--EXECUTE IMMEDIATE tempSql;
35OPEN P_CUR_OUT FOR tempSql;
36
37dbms_output.put_line(tempSql);
38END;

 

 五、ASP.NET实现调用存储过程分页+动态导航栏

案例效果图：

 

源码地址：https://pan.baidu.com/s/1cWyAI1rQalvfcYl4sSv1Tw

提取码：9vcq

 

 

感想 ：如今知识技术繁多迭代快速的时代，程序员应当要有一定的代码和知识积累，每个人的大脑不是计算机不可能永远记得所有东西。

 

以上是 解析分页思想+分页实战 的全部内容， 来源链接： utcz.com/z/534641.html

回到顶部