一、排除Top分页法（自命名，非规范）
思想：所谓“排除Top分页”，主要依靠“排除”和Top这个两大核心步骤。首先查询当前页码之前的数据，然后将该数据从总数据中排除掉，在从剩下的数据中获取前N条记录，就可以得到当前页码的数据。
举例-分页条件：每页显示2条记录，查看第3页
以SQLServer语法实现并说明步骤：
步骤1：编写基础查询语句并按照字段字段进行排序，排序字段会作为后续的数据筛选条件
1 select * from tableName order by id
步骤2：获取一个公式，该公式用于计算当前页码之前所有页面的总记录数
例子的当前页为第3页，那么在第3页之前有2页，并且每页显示2条，最后可得出之前页面总共为：2乘以2等于4条记录。然后根据逻辑得出计算公式：(当前页码-1)*每页显示条数，根据例子分页条件得出：(3-1)*2
步骤3：使用步骤2的公式加到步骤1语句中作为Top的数值，从而可以查出当前页之前的所有数据。
1 select top ((3-1)*2) * from tableName order by id
步骤4：在总数据中排除掉“当前页之前的所有数据”，然后进行排序
1 select * from tableName2 3 Where id not in ( Select top ((3-1)*2) * id from order by id )4 5 Order by id
步骤5：在步骤4的语句中，取出前N条（每页显示的条数）
1 select top 2 * from tableName3 Where id not in ( select top ((3-1)*2) * id from order by id )5 Order by id
SQLite数据库写法：
1 select * from tableName where id2 not in (select did from DishInfo order by did limit (3-1)*2 ) 3 order by did limit 2
注意：SQLite数据库中没有Top关键字，而使用limit关键字来取前N条。
Oracle数据库写法：
1 Select * from 2 Select * from tableName where id not in (3 Select id from (Seletc id from tableName where rownum<=(3-1)*2 order by id ) 4 ) order by id ) where rownum<=2
注意：Oracle数据库中没有Top关键字，而使用rownum来取前N条。
二、游标偏移法（自命名，非规范）
思想和游标的概念比较相似，主要用于SQLite数据库结合 Limit和 Offset关键字来实现。
举例-分页条件：每页显示2条记录，查看第4页
分页思想：
1.求出偏移的起始点，也就从上一页的最后一条数据开始向下偏移。
得出偏移起始点计算公式为：(当前页-1)*每页显示条数
数据例图：
2.获取偏移的数量，也就是等于每页显示的条数，根据示例分页条件得出偏移设立为2。
3.根据偏移起始点和偏移数量得出指定页的数据。
数据例图：
注意：偏移到过的所有行数据即为当前页数据，不包含偏移起始点所在行。
根据思想编写的SQL：
1 select * from tableName order by id Limit 2 OFFSET (4-1)*2
OFFSET ：指定偏移起始点，也就是上一页的最后一行
Limit ：表示偏移数量
该思想和SubString字符串截取的方法思想也比较像，OFFSET相当于定义截取的位置，Limit 相当于截取的数量。
三、指定范围区间获取法（自命名，非规范）
思想：为查询指定的一个唯一连续性的标识，并结合分页条件计算出筛选范围，对标识进行筛选从而得到当前页的数据。
例-分页条件：每页显示2条记录，查看第3页的数据
以SQLServer语法实现如下：
步骤：
1.使用row_number函数为查询数据的每行生成一个唯一连续性的标识，该标识可以用于作为筛选范围的条件字段
1 Select * ,rn=row_number() over (order by 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获取的起始位置和结束位置作为范围条件。根据示例的分页条件最终的语句如下：
1 Select * from (2 3 Select * ,rn=row_number() over (order by id) from tableName4 5 ) temp where rn between (3-1)*2+1 and 3*2
Oracle数据库实现
例-分页条件：每页显示2条记录，查看第3页的数据
Oracle和SQLServer的思想是一致的，主要是在步骤1的时候，oracle生成唯一标识之前需要先进行排序，然后在将查询进行嵌套在外层生成行的唯一标识。
1 Select * from (2 3 Select t.*,rownum as rn (select * from tableName order by id) t4 5 ) where rn between (3-1)*2+1 and 3*2
四、存储过程动态分页
SQLServer存储过程参考代码：
1 --动态数据分页存储过程 2 create proc sp_DataPaging 3 @pageIndex int, --当前页码 4 @pageSize int , --每页显示条数 5 @tableName varchar(200), 6 @orderByField varchar(20), --排序字段 7 @rowCount int output ,--总记录数 8 @pageCount int output --总页数 9 as10 begin11 12 declare @tempSql nvarchar (max),@beginIndex int ,@endIndex int13 14 --1.求总记录数15 set @tempSql='set @rowCount= (select count(*) from '+@tableName+')'16 execute sp_executesql @tempSql, N'@rowCount int output',@rowCount output17 18 /*19 *2.求总页数=总记录数除以每页显示条数20 *乘以1.0是为了让结果为小数，从而通过CEILING将小数向上取整，解决最后一页不满足显示条数的情况21 */22 set @pageCount=CEILING(@rowCount*1.0/@pageSize*1.0)23 24 --3.求出筛选范围25 set @beginIndex=(@pageIndex-1)*@pageSize+126 set @endIndex=@pageIndex*@pageSize27 28 --4.生成查询当前页数据的语句29 set @tempSql=' select * from (30 select *,rd=ROW_NUMBER() over (order by '+@orderByField+') from '+@tableName+' 31 ) temp where rd between '+cast(@beginIndex as varchar)+' and '+cast(@endIndex as varchar) +' '32 33 --5.执行语句查询当前页数据34 exec (@tempSql)35 end36 go37 38 --测试 39 select * from Students order by StudentId 40 declare @pageIndex int, @pageSize int , @rownum int ,@pagenum int ,@tableName varchar(200),@orderByField varchar(20) 41 begin42 set @pageIndex=543 set @pageSize=544 set @tableName='Students'45 set @orderByField='studentid'46 end47 exec sp_DataPaging @pageIndex,@pageSize,@tableName,@orderByField,@rownum output,@pagenum output48 go
Oracle存储过程参考代码：
1 CREATE OR REPLACE PROCEDURE sp_DataPaging 2 ( 3 tableName IN VARCHAR, --表名 4 orderByFiled IN VARCHAR, --排序字段 5 pageIndex IN NUMBER, --当前页码 6 pageSize IN NUMBER, --每页显示条数 7 rowtotal OUT NUMBER , --总条数 8 pageCount OUT NUMBER, --总页数 9 P_CUR_OUT OUT SYS_REFCURSOR10 )11 AS12 tempSql Varchar2(800);13 beginIndex NUMBER;14 endIndex NUMBER;15 BEGIN16 17 --1.求总条数18 tempSql:='select count(*) from '||tableName;19 EXECUTE 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 (30 SELECT t.*,ROWNUM AS rn FROM (SELECT * FROM '||tableName||' ORDER BY '||orderByFiled||' ) t31 ) WHERE rn BETWEEN '||beginIndex||' AND '||endIndex;32 33 --5.执行查询34 --EXECUTE IMMEDIATE tempSql;35 OPEN P_CUR_OUT FOR tempSql;36 37 dbms_output.put_line(tempSql);38 END;
五、ASP.NET实现调用存储过程分页+动态导航栏
案例效果图：
源码地址：https://pan.baidu.com/s/1cWyAI1rQalvfcYl4sSv1Tw
提取码：9vcq
感想 ：如今知识技术繁多迭代快速的时代，程序员应当要有一定的代码和知识积累，每个人的大脑不是计算机不可能永远记得所有东西。
解析分页思想+分页实战转运四方、 vat、 笨鸟转运、 NeoReach、 Wish产品文案优化技巧-优化思路分析、 紧急！Wish 强制规范VAT行为 ，卖家该怎么做？、 2011圣诞节香港十大旅游景点游玩全攻略、 香港海洋公园一日游线路推荐给大家、 专家分析：2017春节赴埃及旅游 土耳其旅游将正常发旅行团、
No comments:
Post a Comment