几种常见SQL分页方式

浏览数：41 / 时间：2015年06月12日

创建环境：

create table pagetest
(
id int identity(1,1) not null,
col01 int null,
col02 nvarchar(50) null,
col03 datetime null
)

--100万记录集(大约耗时5min)
declare @i int
set @i=0
while(@i<1000000)
begin
    insert into pagetest select cast(floor(rand()*10000) as int),left(newid(),10),getdate()
    set @i=@i+1
end

几种分页方式比较：

--写法1，not in/top
--每页50条，取第2015页数据
declare @begin_date datetime
declare @end_date datetime
select @begin_date = getdate()
select top 50 * from pagetest 
where id not in (select top ((2015-1)*50) id from pagetest order by id)
order by id
select @end_date = getdate()
select datediff(ms,@begin_date,@end_date) as ‘ms‘

结果：473 ms

--写法2，not exists
declare @begin_date datetime
declare @end_date datetime
select @begin_date = getdate()
select top 50 * from pagetest 
where not exists 
(select 1 from (select top ((2015-1)*50) id from pagetest order by id)a  where a.id=pagetest.id)
order by id
select @end_date = getdate()
select datediff(ms,@begin_date,@end_date) as ‘ms‘

结果：483 ms

--写法3，max/top
declare @begin_date datetime
declare @end_date datetime
select @begin_date = getdate()
select top 50 * from pagetest
where id>(select max(id) from (select top ((2015-1)*50) id from pagetest order by id)a)
order by id
select @end_date = getdate()
select datediff(ms,@begin_date,@end_date) as ‘ms‘

结果：343 ms

--写法4，row_number() 只支持sql2005及以上版本
declare @begin_date datetime
declare @end_date datetime
select @begin_date = getdate()
select top 50 * from 
(select row_number()over(order by id)rownumber,* from pagetest)a
where rownumber>((2015-1)*50)
select @end_date = getdate()
select datediff(ms,@begin_date,@end_date) as ‘ms‘

结果：293 ms

以上测试在不同环境在会有略微差别，整体来说都还不错，row_number和max/top方式性能相对更好些，而row_number只支持sql2005版本及以上，所以max/top在大部分环境下会是比较好的选择。

简单将ROWNUMBER,max/top的方式封装到存储过程。

create proc [dbo].[spSqlPageByRownumber]
@tbName varchar(255),        --表名
@tbFields varchar(1000),      --返回字段（可以为*）
@PageSize int,                --页尺寸
@PageIndex int,                --页码
@strWhere varchar(1000),    --查询条件(为空则无条件)
@StrOrder varchar(255),  --排序条件
@Total int output            --返回总记录数
as
declare @strSql varchar(5000)    --主语句
declare @strSqlCount nvarchar(500)--查询记录总数主语句

--------------总记录数---------------
if @strWhere !=‘‘
begin
set @strSqlCount=‘Select @TotalCout=count(*) from  ‘ + @tbName + ‘ where ‘+ @strWhere
end
else
begin
set @strSqlCount=‘Select @TotalCout=count(*) from  ‘ + @tbName
end
--------------分页------------
if @PageIndex <= 0
begin
  set @PageIndex = 1
end

set @strSql=‘Select * from (Select  row_number() over(‘+@strOrder+‘) rowId,‘+ @tbFields
+‘ from ‘ + @tbName + ‘ where 1=1 ‘ + @strWhere+‘ ) tb where tb.rowId >‘+str((@PageIndex-1)*@PageSize)
+‘ and tb.rowId <= ‘ +str(@PageIndex*@PageSize)

exec sp_executesql @strSqlCount,N‘@TotalCout int output‘,@Total output
exec(@strSql)

create proc [dbo].[spSqlPageByMaxTop]
@tbName varchar(255),        --表名
@tbFields varchar(1000),      --返回字段（可以为*）
@PageSize int,                --页尺寸
@PageIndex int,                --页码
@strWhere varchar(1000),    --查询条件(为空则无条件)
@StrOrder varchar(255),  --排序条件
@Total int output            --返回总记录数
as
declare @strSql varchar(5000)    --主语句
declare @strSqlCount nvarchar(500)--查询记录总数主语句

--------------总记录数---------------
if @strWhere !=‘‘
begin
set @strSqlCount=‘Select @TotalCout=count(*) from  ‘ + @tbName + ‘ where ‘+ @strWhere
end
else
begin
set @strSqlCount=‘Select @TotalCout=count(*) from  ‘ + @tbName
end
--------------分页------------
if @PageIndex <= 0
begin
  set @PageIndex = 1
end

set @strSql=‘select top ‘+str(@PageSize)+‘ * from ‘ + @tbName + ‘
where id>(select max(id) from (select top ‘+str((@PageIndex-1)*@PageSize)+‘ id from ‘ + @tbName + ‘‘+@strOrder+‘)a)
‘+@strOrder+‘‘

exec sp_executesql @strSqlCount,N‘@TotalCout int output‘,@Total output
exec(@strSql)