SQL Server数据库分页查询

柔情只为你懂 2021-12-10 03:57 385阅读 0赞

原文链接：[SQLServer数据库分页查询][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万：

![642227-20170111172305916-1006615714.png][]

五、结论

1.max/top,ROW\_NUMBER()都是比较不错的分页方法。相比ROW\_NUMBER()只支持sql2005及以上版本，max/top有更好的可移植性，能同时适用于sql2000,access。

2.not exists感觉是要比not in效率高一点点。

3.ROW\_NUMBER()的3种不同写法效率看起来差不多。

[SQLServer]: https://www.cnblogs.com/hanmian4511/p/6274200.html
[642227-20170111172305916-1006615714.png]: /images/20211209/1989a80ec7094958814815518944b103.png