Oracle、 Mysql 、 SQLserver 分頁查詢

mysql 分頁最簡單了.

select

*

from

account 

where

(usertype='base' or usertype='home' or usertype='salse') 

and logindate is not null order by logindate desc

limit 起始行, 每頁多少行

limit 接受一個或兩個數字參數。

參數必須是一個整數常量。

如果給定兩個參數，第一個參數指定第一個傳回記錄行的偏移量，

第二個參數指定傳回記錄行的最大數目。

初始記錄行的偏移量是 0(而不是 1)

sqlserver 的資料分頁:

假設現在有這樣的一張表：

create table test

(

 id int primary key not null identity,

 names varchar(20)

)

然後向裡面插入大約1000條資料，進行分頁測試

假設頁數是10，現在要拿出第5頁的内容，查詢語句如下：

--10代表分頁的大小

select top 10 *

from test

where id not in

 --40是這麼計算出來的：10*(5-1)

 select top 40 id from test order by id

order by id

原理：需要拿出資料庫的第5頁，就是40-50條記錄。首先拿出資料庫中的前40條記錄的id值，然後再拿出剩餘部分的前10條元素

第二種方法：

還是以上面的結果為例，采用另外的一種方法

--資料的意思和上面提及的一樣

where id >

 select isnull(max(id),0)

 from 

  (

   select top 40 id from test order by id

  ) a

原理：先查詢前40條記錄，然後獲得其最id值，如果id值為null的，那麼就傳回0

然後查詢id值大于前40條記錄的最大id值的記錄。

這個查詢有一個條件，就是id必須是int類型的。

第三種方法：

from 

 select row_number() over(order by id) as rownumber,* from test

) a

where rownumber > 40

原理：先把表中的所有資料都按照一個rownumber進行排序，然後查詢rownuber大于40的前十條記錄

這種方法和oracle中的一種分頁方式類似，不過隻支援2005版本以上的

第四種：

存儲過程查詢

建立存儲過程

alter procedure pagedemo

@pagesize int,

@page int

as

declare @temp int

set @temp=@pagesize*(@page - 1)

begin

 select top (select @pagesize) * from test where id not in (select top (select @temp) id from test) order by id

end

執行存儲過程

exec 10,5

oracle分頁查詢語句（一）

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控制分頁查詢的每頁的範圍。

上面給出的這個分頁查詢語句，在大多數情況擁有較高的效率。分頁的目的就是控制輸出結果集大小，将結果盡快的傳回。在上面的分頁查詢語句中，這種考慮主要展現在where rownum <= 40這句上。

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

where rn between 21 and 40

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

這是由于cbo優化模式下，oracle可以将外層的查詢條件推到内層查詢中，以提高内層查詢的執行效率。對于第一個查詢語句，第二層的查詢條件where rownum <= 40就可以被oracle推入到内層查詢中，這樣oracle查詢的結果一旦超過了rownum限制條件，就終止查詢将結果傳回了。

而第二個查詢語句，由于查詢條件between 21 and 40是存在于查詢的第三層，而oracle無法将第三層的查詢條件推到最内層（即使推到最内層也沒有意義，因為最内層查詢不知道rn代表什麼）。是以，對于第二個查詢語句，oracle最内層傳回給中間層的是所有滿足條件的資料，而中間層傳回給最外層的也是所有資料。資料的過濾在最外層完成，顯然這個效率要比第一個查詢低得多。

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

這裡就不對包含排序的查詢進行說明了，下一篇文章會通過例子來詳細說明。下面簡單讨論一下多表聯合的情況。對于最常見的等值表連接配接查詢，cbo一般可能會采用兩種連接配接方式nested loop和hash join（merge join效率比hash join效率低，一般cbo不會考慮）。在這裡，由于使用了分頁，是以指定了一個傳回的最大記錄數，nested loop在傳回記錄數超過最大值時可以馬上停止并将結果傳回給中間層，而hash join必須處理完所有結果集（merge join也是）。那麼在大部分的情況下，對于分頁查詢選擇nested loop作為查詢的連接配接方法具有較高的效率（分頁查詢的時候絕大部分的情況是查詢前幾頁的資料，越靠後面的頁數通路幾率越小）。

是以，如果不介意在系統中使用hint的話，可以将分頁的查詢語句改寫為：