高效数据库分页存储过程详解
假如我们有一百条数据,现在要分二十页显示,一页显示五条。
第一页:1至5条,第二页:6至10条,第三页:10至15条。
分页存储过程思路分析:
在上一篇分页存储过程详解的文章里我们介绍的思路是这样的,取第一页数据的时候,top 5就可以了,取第二页数据的时候top 10,然后 id not in 前五条,取第三页数据的时候就是 top 15,然后not in 前十条数据,那么我们取第二十页数据的时候,就是top 100,然后not in前95条数据。
可以看到上面这种方法,随着数据量的增大 not in计算的数据也要增大,而且有的时候有条件的话还需要一次性把所有的数据取出来,然后再排序取前面几条,然后再not in,而下面介绍这种方式完全不需要把全部数据取出来。
高效分页存储过程思路分析:
如何不通过not in方法取出自己想要的数据呢,很简单,我们只需要用top就可以了,说一下简单的思路,比如取第6条到第十条数据的时候我们可以先取前十条数据,按时间排序(top 10),然后我们再从这十条数据里按时间倒序排序取前五条数据,这样取出来的数据就是从10到6条了,然后我们再正序排一下序就是从6到10条了,怎么样,简单吧,下面我们来写一下这条sql语句。
高效分页存储过程Sql语句讲解:
我们还是举上面的例子,取从第6到第10条数据:
第一步,我们要取从第一到第十条数据:select top 10 * from table order by id
第二步,我们在这十条数据里反取五条:
select top 5 * from table where id in (select top 10 id from table order by id ) order by id desc
Ok,运行下以上这条句话,我们已经得到第6到第10条了,只不过顺序是反的,那么我们再排一下序给正过来就可以了。
第三步,正序排序数据:
select top 5 * from table where id in
(select top 5 id from table where id in (select top 10 id from table order by id ) order by id desc)
order by id
好了,到这里,我们的第六条到第十条,也就是第二页的内容就算是取出来了。
下面我们要想办法把这个代码构造成一个存储过程,通用于任何表,任何条件,任何条数的数据,这样的话,我们就有几个地方需要改成变量了,首先每页的条数需要改成变量,还有就是表名和条件都要改成变量,最后我们还要把排序字段也要改成变量。
构造存储过程详细代码:
创建存储过程之前先定义变量
CREATE procedure SplitPage(
@ColList varchar(4000)='', --显示字段
@Table VARCHAR(4000)='',--查询的表,from后面的语句
@Where varchar(4000)='',--查询条件,where后面的语句
@Sort VARCHAR(255)='',--排序条件,不带表名的字段名且不带asc,desc,
@AscDesc VARCHAR(255)='',--排序asc,desc,
@PageSize int=0,--每页记录数
@PageIndex int=0,--当前页码
@ID VARCHAR(255)='',--ID字段,例如"memberid"
)
--每个变量后面都加了详细的注释,这个不用我解释了吧,如果你还不明白的话,看看后面我们怎么用就行了。
DECLARE @Sql nVARCHAR(4000)
if(@Where='')
begin
set @Where='1=1'
end
--如果条件为空的话则给一个1=1永远为真的条件,你也可以在下面的语句中把where删除掉,从页面上传进来
set @Sql='select top '+CAST(@PageSize AS VARCHAR(20))+' '+@ColList+' from '+@Table+' where '+@ID+' in
(
select top '+CAST(@PageSize AS VARCHAR(20))+' '+@ID+' from '+@Table+' where '+@ID+' in
(select top '+CAST((@PageSize*@PageIndex) AS VARCHAR(20))+' '+@ID+' from '+@Table+' where '+@Where+' order by '+@Sort+' '+@AscDesc+')
order by '+@ID+' asc
) order by '+@ID+' '+@AscDesc
EXEC(@Sql)--执行sql语句
总结:
OK,到这里我们就算是把这个高效的分页存储过程讲解完了,但是还有一个小小的不足之处,排序是自已写好的,没有从页面上传进来,你可以再定义一个变量存储包含desc或asc的变量然后完成按要求排序。
此文摘自tianshixiadao百度空间,谢谢!