資料庫分頁查詢方法

　　在這裡主要講解一下MySQL、SQLServer2000(及SQLServer2005)和ORCALE三種資料庫實現分頁查詢的方法。

可能會有人說這些網上都有，但我的主要目的是把這些知識通過我實際的應用總結歸納一下，以方便大家查詢使用。

　　下面就分別給大家介紹、講解一下三種資料庫實現分頁查詢的方法。

　　一、 MySQL 資料庫分頁查詢

　　MySQL資料庫實現分頁比較簡單，提供了LIMIT函數。一般只需要直接寫到sql語句後面就行了。

　　LIMIT子句可以用來限制由SELECT語句返回過來的資料數量，它有一個或兩個參數，如果給出兩個參數， 第一個參數指定返回的第一行在所有資料中的位置，從0開始（注意不是1），第二個參數指定最多返回行數。例如：

　　select * from table WHERE … LIMIT 10; #返回前10行

　　select * from table WHERE … LIMIT 0,10; #返回前10行

　　select * from table WHERE … LIMIT 10,20; #返回第10-20行資料

　　二、 SQLServer資料庫分頁查詢

　　SQLServer資料庫又分為SQLServer2000和SQLServer2005。一般比較簡單的方法是通過TOP函數來實現。如下：

　　SELECT TOP 10 * FROM sql WHERE (

　　code NOT IN (SELECT TOP 20 code FROM TestTable ORDER BY id))

　　ORDER BY ID

　　這條語句，從理論上講，整條語句的執行時間應該比子句的執行時間長，但事實相反。因為，子句執行後返回的是20條記錄，而整條語句僅返回10條 語句，所以影響資料庫回應時間最大的因素是物理I/O操作。而限制物理I/O操作此處的最有效方法之一就是使用TOP關鍵詞了。TOP關鍵詞是SQL SERVER中經過系統最佳化過的一個用來提取前幾條或前幾個百分比資料的詞。

　　以上語句的有一個致命的缺點，就是它含有NOT IN字樣，要換成用not exists來代替not in，二者的執行效率實際上是沒有區別的。

　　在以上分頁演算法中，影響我們查詢速度的關鍵因素有兩點：TOP和NOT IN。TOP可以提高我們的查詢速度，而NOT IN會減慢我們的查詢速度，所以要提高我們整個分頁演算法的速度，就要徹底改造NOT IN，同其他方法來替代它。

　　我們知道，幾乎任何欄位，我們都可以通過max(欄位)或min(欄位)來提取某個欄位中的最大或最小值，所以如果這個欄位不重複，那麼就可以 利用這些不重複的欄位的max或min作為分水嶺，使其成為分頁演算法中分開每頁的參照物。在這裡，我們可以用操作符“>”或“<”號來完成這 個使命。如：

　　Select top 10 * from table1 where id>200

　　於是就有了如下分頁方案：

　　select top 頁大小 *

　　from table1

　　where id>

　　(select max (id) from

　　(select top ((頁碼-1)*頁大小) id from table1 order by id) as T

　　)

　　order by id

　　這種方法執行多少始終沒有大的降勢，後勁仍然很足。尤其對於資料量大的時候，該方法執行速度一點也不會降低。

　　使用TOP要求主鍵必須唯一，不能是聯合主鍵。如果是聯合主鍵，則查詢出的結果會亂序的。

　　目前SQLServer2005提供了一個row_number()函數。ROW_NUMBER() 就是產生一個順序的行號,而他產生順序的標準,就是後面緊跟的OVER(ORDER BY ReportID)，其中ReportID可以是聯合主鍵。下面,我們看看怎麼具體應用這個RowNo進行分頁.

　　SELECT TOP 10 * FROM

　　(

　　SELECT top 10 ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo

　　FROM TABLE

　　) AS A

　　WHERE RowNo > " + pageIndex*10

　　pageIndex就是我們需要資料的頁數.

　　但對於SQLServer2000的話，如果是聯合主鍵，我還沒有解決辦法，如果大家有可跟我聯絡。謝謝大家了。

　　三、 ORCALE資料庫分頁查詢

　　ORCALE資料庫實現分頁查詢可以使用row_number()函數或者使用rownum 虛列兩種方法。

　　第一種：利用分析函數row_number() 方法

　　select * from(

　　select t.*,row_number() over (order by t1.id) rowno from TABLE1

　　)

　　where rowno between 21 and 40;

　　第二種：直接使用rownum 虛列

　　select * from

　　(select t.*,rownum as rowno from TABLE1 )

　　where rowno between 10 and 20

　　這兩種方法比較，顯然第二種方法比較好。因為不用order by語句，會提高檢索資料的速度的，尤其資料量越大時，第二種方法快速檢索資料越明顯。

　　最後提醒大家：oracle中慎用帶有order by的分頁。尤其是在oracle10g中，會出現會引起混亂，即相同記錄會出現在不同頁中。