淺淡SqlServer的Top與Oracle的RowNum

       平時的項目開發中，分頁預存程序是用的比較多的預存程序,SqlServer分頁預存程序中經常要用到top,Oracle中則經常用到了RowNum.

       現在，有一個UserInfo表，一個欄位是UserId,另一個欄位是UserName,其中是UserId是自動成長的,步長是1.表中共有30條資料，其中UserId的值不一定是連續的。現在要實現的目的是取其中的第11至第20條記錄。先看SqlServer的幾種做法:

       第一種寫法：

select top 10 *  
 from UserInfo  
 where UserId in 
       (
        select top 20 UserId 
        from UserInfo
       ) 
 order by UserId desc

 

      第二種寫法：

 select top 10 * from UserInfo where UserId not in  

 (select top 10 UserId from UserInfo )

 

      第三種寫法：

 select top 10 * from UserInfo where UserId>
 (select max(UserId) from   

 (select top10 UserId from UserInfo order by UserId) a)

 

     第四種寫法（只可在Sqlserver 2005中)：

select * from (select Row_Number() over     

(Order by UserId) as RowId ,* from UserInfo) U 
where U.RowId between 10 and 20

 

     Sqlserver 中其實還有另外幾種寫法，不一一寫出。四種方法中，後兩種的寫法要比前兩種寫法效率要高些，但第四種只能寫在SqlServer 2005中。

    在看Oracle中實現取其中的第11至第20條記錄的做法之前，先看看一些人在使用RowNum遇到的莫名其妙的怪事。表同樣是UserInfo,30條資料

select t.* from userinfo t where rownum>10

    查詢結果：

    

    理論上應該是有20條資料才對啊，問題出現在哪呢？

    因為ROWNUM是對結果集加的一個偽列，即先查到結果集之後再加上去的一個列 (這裡要強調的一點是：先要有結果集)。簡單的說 rownum 是對符合條件結果的序號。所以對於rownum>10沒有資料是否可以這樣理解：

    ROWNUM 是一個序列，是oracle資料庫從資料檔案或緩衝區中讀取資料的順序。它取得第一條記錄則rownum值為1，第二條為2，依次類推。如果你用>,>=,=,between...and這些條件，因為從緩衝區或資料檔案中得到的第一條記錄的rownum為1，則被刪除，接著取下條，可是它的rownum還是1，又被刪除，依次類推，最後的查詢結果為空白。

    再看下面一條sql語句：

select t.* from userinfo t where rownum!=10

    查詢結果：

 

     查出的來結果不是21條，而是9條。可以這樣理解：rownum 為9後的記錄的 rownum為10，因條件為 !=10，所以去掉，其後記錄補上，rownum又是10，也去掉，一直這樣下去，最後的結果只有9條了。

    如果把後面的條件改為 where rownum>1 時，會發現查不到一條資料，如果是where rownum>0 或是where rownum>＝1時則可以查詢到所有的資料。原因很簡單：因為 rownum 是在查詢到的結果集後加上去的，它總是從1開始。

     between 1 and 20 或者 between 0 and 20 能查到結果，而用 between 2 and 20 卻得不到結果，原因同上一樣，因為 rownum 總是從 1 開始。

     所以要實現取UserInfo表其中的第11至第20條記錄,可以這樣寫：

select *  from 
(select rownum as rn,t.* from userinfo t where rownum >0)
where rn between 10 and 20

查詢結果：

     

當然也可以這樣寫：

 select   *   from   UserInfo   where   rownum<20     
   minus     
  select   *   from   UserInfo   where   rownum<10

這種寫法沒有前面那種效率高。

 

但不能這樣寫：

select t.* from UserInfo t where rownum between 10 and 20
select t.* from UserInfo t where rownum > 10 and rownum <=20

上 面兩種寫法都取不到資料的。 

     對於Sqlserver 和Oracle實現取表其中的第11至第20條記錄如果有更好的寫法 ，可以貼出來學習下。