SqlServer与Oracle的分页(收集整理)

    分页是软件开发常用的功能之一,当然也可以使用假分页来实现(数据全提取出来,在前台实现分页,适合小量数据),不过，真分页效率可能会更高一些.今天有时间收集整理了一下Sql server与Oracle的几种常见分页.由于本文资料来源较多,较杂.没有注明出处，不当之处，还望原作者见谅.

  一.测试数据的建立

      测试表test1结构如下:

     

     测试表test1数据如下:

    

     以下的语句示例，假设每页是2条数据,我取第2页的数据.即取第3和第4条数据

  二.SQLSERVER分页的四种实现

      方法1

        select top 2 * from test1 where did not in (select top 2 did from test1)

      方法2

        select top 2 * from test1 where did > (select isnull(max(did),0) from (select top 2 did from test1) a)

      方法3

       select * from (select row_number() over(order by did) as rownum,did,value from test1) a where  a.rownum>2 and a.rownum<=4

      方法4

       select * from test1 where did in  (select did from (select top 2 did from test1 union all select top 4 did from test1) a group by did having count(did)=1)

 三.ORACLE分页的三种实现

       方法1

        select * from (select A.*,rownum rn from test1 A )  where rn>=2 and rn<4  

       方法2

        select * from (select A.*,row_number() over (order by did) rn from test1 A) where rn>=2 and rn<4

       方法3

        select * from test1 where rownum<4 minus select * from test1 where rownum<2

 

    备注

           1.作者写的只是方法，没有过多考虑效率.每个方法的语句有待读者自己改写

           2.至于不同方法的效率问题，有待读者自己去测.