萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mssql數據庫 >> 詳解SQLServer和Oracle的分頁查詢

詳解SQLServer和Oracle的分頁查詢

不管是DRP中的分頁查詢代碼的實現還是面試題中看到的關於分頁查詢的考察,都給我一個提示:分頁查詢是重要的。當數據量大的時候是必須考慮的。之前一直沒有花時間停下來好好總結這裡。現在又將Oracle視頻中關於分頁查詢的內容看了一遍,發現很容易就懂了。

1.分頁算法
    最開始我在網上查找資料的時候,看到很多分頁內容,感覺很多很亂。其實不是這樣。網上那些資料大同小異。問題出在了我自己這裡。我沒搞明白進行分頁的前提是什麼?我們都知道只要有分頁都會涉及這些變量:每頁又多少條記錄(pageSize)、當前頁(pageNow)、總記錄數(totalRecords)、總頁數(totalPages)、開始頁(beginRow)、結束頁(endRow)。網上的那些資料分頁算法有用到pageSize的,有用到beginPage還有用到endPage.其實這些變量需要分類:我將他們分為三類
    A.需要從數據庫中查詢出來的:totalRecords. " select count(*) from tableName"
    B.最基本的需要用戶提供的:pageSize和pageNow.(個人覺得這是分頁算法的前提)
    C.從其他變量計算得來的:totalPages、beginRow和endRow.(這裡需要計算出beginRow和endRow是由於分頁查詢中需要用到,totalPages是頁面需要提供的信息)。具體的計算公式:

totalPages: if ((totalRecords% pageSize) == 0) {  
             totalPages = totalRecords/ pageSize;  
           } else {  
             totalPages = totalRecords/ pageSize + 1;  
           } 
beginRow: (pageNow-1) * PageSize +1 
endRow:   pageNow * PageSize 

這樣這些變量的值就都可以獲得了。具體怎麼使用請接著看2和3部分。

2.Oracle中的常用分頁方法
   其實不管是Oracle還是SQLServer,實現分頁查詢的基礎都是子查詢。用我自己的話說就是:select中套select。
   Oracle分頁方式有三種。我這裡只講一種容易理解的。以員工表(emp)為例。假設有10條記錄,現在分頁要求每頁5條記錄,當前頁為2.則查詢出來的是記錄為6-10。我們先用具體的數字做,然後再換成變量。
   Oracle實現第一步:select a.*,rownum rn from (select * from emp) a;其中rownum是Oracle內部分配行號。括號中的select * from emp是將emp表中的記錄全部查詢出來。然後我們再將查詢出來的結果作為視圖進一步查詢。外面的select除了查詢emp的全部以外再加一個rownum,以便後面的查詢使用。
   Oracle實現第二步:select a.*,rownum rn from (select * from emp) a where rownum<=10 ;第二步加條件查詢出行號小於等於10的記錄。這裡可能會有這樣的疑問為什麼不直接寫rownum>=6 and rownum<=10.不就解決問題了。這裡Oracle內部機制不支持這種寫法。
   Oracle實現第三步:select * from (select a.*,rownum rn from (select * from emp) a where rownum<=10) where rn>=6 ;ok,這樣就可以完成查詢6-10條記錄了。
   最後。我們轉換為變量。可能是在java程序中也可能是在pl/sql中。
   需要轉換的又三個:“emp”的位置為具體表名、“6”的位置  為(pageNow-1) * PageSize +1 、“10"的位置 為 pageNow * PageSize。
   這種方式可以作為模板使用,修改起來很方便。所有改動只需要改動最裡層就可以了。比如查詢指定列的情況:修改最裡層select ename,sal from emp;根據薪水列排序:select ename,sal from emp order by sal;都只需要修改最裡層。

3.SQLServer中的常用分頁方法
   我們還是采用員工表的例子講SQLServer中分頁的實現
   第一種TOP的使用:
SQLServer實現第一步:select top 10 * from emp order by empid ;按照員工ID升序排列,取出前10條記錄。
SQLServer實現第二步:select top 5* from (select top 10 * from emp order by empid ) a order by empid desc 。將取出的10條記錄按員工號降序排列再取出5條記錄。這裡的第一次用升序排序,第二次用降序排序是巧妙之處。沒有想到top能起到這樣的效果。這裡的10的位置用變量pageNow * PageSize代替而5用PageSize 代替。
   第二種Top和In的使用:
select top 5 * from emp where empid in (select top 10 empid from emp order by empid) order by empid desc;    這裡的10的位置用變量pageNow * PageSize代替而5用PageSize 代替。
   其他查詢都是大同小異的,這裡不再贅述。

以上就是兩種數據庫實現分頁功能的案例,希望對大家的學習有所幫助。

copyright © 萬盛學電腦網 all rights reserved