常用資料庫（MsSql,Oralce,MySql）用SQL實現分頁查詢

一、SQL Server
        從資料庫表中的第M條記錄開始取N條記錄，利用Top關鍵字：注意如果Select語句中既有top，又有order by，則是從排序好的結果集中選擇：
        語句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 20 id
                 FROM TestTable
                 ORDER BY id) AS T))
ORDER BY ID

SELECT TOP 頁大小 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 頁大小*頁數 id
                 FROM 表
                 ORDER BY id) AS T))
ORDER BY ID   二、SQL Server 2005

在Sql Server 2005中，我們可以利用新增函數row_number()來更高效的實現分頁儲存

select * from (
　　select row_number() over (order by StudentInfoId) row,* from StudentInfo ) StudentInfo
　　where row between @PageSize*(@PageIndex-1) and @PageSize*@PageIndex

或者

      SELECT TOP 10 *
      FROM
      (
       SELECT top 10 [InstanceName], [UserName], [ReportID],
       [TimeStart], [TimeEnd],ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo
       FROM [ExecutionLog]
      ) AS A
      WHERE RowNo > " + pageIndex*10
    pageIndex就是我們需要資料的頁數，10是每頁顯示的記錄數。

三、Oralce資料庫

一種通用的分頁SQL格式為（該方法也是Hibernate中針對Oracle採用的方式）：

SELECT * FROM  

(  

SELECT A.*, ROWNUM RN  

FROM (SELECT * FROM TABLE_NAME) A  

WHERE ROWNUM <= 40  

)  

WHERE RN >= 21 

 

其中最內層的查詢SELECT * FROM TABLE_NAME表示不進行翻頁的原始查詢語句。ROWNUM <= 40和RN >= 21控制分頁查詢的每頁的範圍。

上面給出的這個Oracle分頁查詢語句，在大多數情況擁有較高的效率。分頁的目的就是控制輸出結果集大小，將結果儘快的返回。在上面的分頁查詢語句中，這種考慮主要體現在WHERE ROWNUM <= 40這句上。

選擇第21到40條記錄存在兩種方法，一種是上面例子中展示的在查詢的第二層通過ROWNUM <= 40來控制最大值，在查詢的最外層控制最小值。而另一種方式是去掉查詢第二層的WHERE ROWNUM <= 40語句，在查詢的最外層控制分頁的最小值和最大值。這是，查詢語句如下：

SELECT * FROM  

(  

SELECT A.*, ROWNUM RN  

FROM (SELECT * FROM TABLE_NAME) A  

)  

WHERE RN BETWEEN 21 AND 40 

 

對比這兩種寫法，絕大多數的情況下，第一個查詢的效率比第二個高得多。

這是由於CBO最佳化模式下，Oracle可以將外層的查詢條件推到內層查詢中，以提高內層查詢的執行效率。對於第一個查詢語句，第二層的查詢條件 WHERE ROWNUM <= 40就可以被Oracle推入到內層查詢中，這樣Oracle查詢的結果一旦超過了ROWNUM限制條件，就終止查詢將結果返回了。

而第二個查詢語句，由於查詢條件BETWEEN 21 AND 40是存在於查詢的第三層，而Oracle無法將第三層的查詢條件推到最內層（即使推到最內層也沒有意義，因為最內層查詢不知道RN代表什麼）。】因此，對於第二個查詢語句，Oracle最內層返回給中介層的是所有滿足條件的資料，而中介層返回給最外層的也是所有資料。資料的過濾在最外層完成，顯然這個效率要比第一個查詢低得多。

上面分析的查詢不僅僅是針對單表的簡單查詢，對於最內層查詢是複雜的多表聯集查詢或最內層查詢包含排序的情況一樣有效。

關於Oracle的ROWNUM的一點說明：

在Oracle中，ROWNUM不應該直接應用於>,>=,=,between...and，只能用以符號(<、<=、!=)，並非說用>, >=,=,between..and 時會提示SQL語法錯誤，而是經常是查不出一條記錄來。但如果就是想要用
ROWNUM > 10 這種條件的話話就要用嵌套語句,把 ROWNUM 先產生並給其命名別名，然後對他進行查詢。

select *

from (selet rownum as rn，t1.* from a where ...)

where rn >10

四、My sql資料庫
        My sql資料庫最簡單，是利用mysql的LIMIT函數,LIMIT [offset,] rows從資料庫表中M條記錄開　　　         始檢索N條記錄的語句為：
        SELECT * FROM 表名稱 LIMIT M,N

         例如從表Sys_option（主鍵為sys_id)中從10條記錄還是檢索20條記錄，語句如下：
         select * from sys_option limit 10,20