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.至於不同方法的效率問題，有待讀者自己去測.