SQLServer数据库分页查询

一、创建表结构
CREATE TABLE [dbo].[a](
    [id] [int] NOT NULL,
    [name] [nvarchar](50) NULL,
    [age] [int] NULL)
二、添加测试数据
declare @i int
set @i=1
while(@i<10000)
begin
    insert into a select @i,left(newid(),7),12
    set @i += 1
end
三、分页sql,下面例子是每页10条,取第31-40条数据。
--方法一:Order by
Select Top (40-31+1)  * From a Where ID in (Select Top 40 ID From a Order by ID  ) Order by ID Desc

--方法二:not in/top
select top 10 * from a where id not in (select top 30 id from a order by id) order by id

--方法三:not exists 
select top 10 * from a where not exists (select 1 from (select top 30 id from a order by id)a1 where a1.id=a.id) order by id

--方法四:max/top
select top 10 * from a where id>(select max(id) from (select top 30 id from a order by id)a1) order by id

--方法五:row_number()
select top 10 * from (select row_number()over(order by id)rownumber,* from a)a1 where rownumber>30
select * from (select row_number()over(order by id)rownumber,* from a) a1 where rownumber>30 and rownumber<41
select * from (select row_number()over(order by id)rownumber,* from a)a1 where rownumber between 31 and 40

--方法六:row_number() 变体,不基于已有字段产生记录序号,先按条件筛选以及排好序,再在结果集上给一常量列用于产生记录序号
select *
from (select row_number()over(order by id)rownumber,* from (select top 40 * from a where 1=1 order by id)a
)b where rownumber>30
四、SQL语句效率测试
declare @begin_date datetime
declare @end_date datetime
select @begin_date = getdate()

<.....YOUR CODE.....>

select @end_date = getdate()
select datediff(ms,@begin_date,@end_date) as '毫秒'
1万:基本感觉不到差异10万:五、结论1.max/top,ROW_NUMBER()都是比较不错的分页方法。相比ROW_NUMBER()只支持sql2005及以上版本,max/top有更好的可移植性,能同时适用于sql2000,access。2.not exists感觉是要比not in效率高一点点。3.ROW_NUMBER()的3种不同写法效率看起来差不多。

相关内容推荐