环境: winNT32 ,sqlserver2005
众所周知,当我们需要在网页展示数据时,经常会设计到分页的功能,分页很多种实现方法,每种数据库都有自己独有的分页方式,现在笔者以sqlserver为例,解释在sqlserver服务器下如何使用T-sql来实现分页机制:
分页基本原理: 在分页技术中,我们需要得到至关重要的两个数据,1,当前页码号,2,每页显示数据的量,,有了这两个数据,即可以定位虚拟表内的一段数据,最早的分页算法是基于top配合not in 语法来实现,这种分页查询的效率极其低下,我们无法使用此种算法用作拥有大量数据的虚拟表,在sqlserver2005中,有函数row_number() ,此函数的作用是在虚拟表内增加一个字段,此字段按顺序标识了每一条数据,在每次查询时会重新生成新的row_number(),再配合between xx and xx则可以实现分页。
为了测试此理论是否成立,我们写一段t-sql语句,插入500万条数据在表内,然后对其进行查询。
declare @count int
set @count = 1
while @count<=5000000
begin
insert into test ('test',getdate(),'this is crazy test')
set @count=@count+1
end
执行完后,test表中含有500万条数据,并且每条数据都具有唯一性,
以下为row_number()分页存储过程
create proc Pager_Proc
@pageIndex int,
@pageSize int,
@tableName varchar(20),
@mID varchar(20)
as
declare @sql varchar(200)
declare @startNumber int
declare @endNumber int
set @startNumber = (@pageIndex-1)*@pageSize+1
set @endNumber = @pageIndex*@pageSize
set @sql = 'select * from
(select *,ROW_NUMBER() over(order by '+@mID+' asc) as number from '+@tableName+') as test
where test.number between '+str(@startNumber)+' and '+str(@endNumber)+''
exec(@sql)
其中我们声明了startNumber和endNumber,这是和top查询所不一样的地方,在between and中需要两个数字型变量,我们可以通过
set @startNumber = (@pageIndex-1)*@pageSize+1
set @endNumber = @pageIndex*@pageSize
来算出我们需要从多少开始,到多少结束。
以下为调用过程
exec Pager_Proc 50000,100,'test','testID'
其中我们需要指定表名test 和 主键列testID
参数一:从第5万条数据开始
参数二:查出100条。