Oracle, DB2 及 MySQL 分頁查詢寫法

 

現在在WEB 應用中使用分頁技術越來越普遍了，其中利用資料庫查詢分頁是一種效率比較高的方法，

下面列出了Oracle, DB2  及 MySQL 分頁查詢寫法。

 

一：Oracle
select * from (select rownum,name from table where rownum <=endIndex )
where rownum > startIndex

二：DB2
DB2分頁查詢
SELECT * FROM (Select 欄位1,欄位2,欄位3,rownumber() over(ORDER BY 排序用的列名 ASC) AS rn from 表名) AS a1 WHERE a1.rn BETWEEN 10 AND 20

以上表示提取第10到20的紀錄

select * from (select rownumber() over(order by id asc ) as rowid from table where rowid <=endIndex ) AS a1
where a1.rowid > startIndex

三：MySQL

select   *   from   table   limit   start,pageNum  

SELECT EVENTID  , EVENTNAME  , CREATETIME  , ELAPSEDTIME  , REPEATCOUNT  , MESSAGE  , LOCALINSTANCEID , STATUS , MEMO FROM(SELECT rownumber() OVER (ORDER BY CREATETIME ASC) AS rowid FROM MS_EVENT WHERE rowid <=20) as a1 WHERE a1.rowid >10

SELECT * FROM (SELECT EVENTID  , EVENTNAME  , CREATETIME  , ELAPSEDTIME  , REPEATCOUNT  , MESSAGE  , LOCALINSTANCEID , STATUS , MEMO,
ROWNUMBER() OVER (ORDER BY CREATETIME  ASC) AS rn FROM MS_EVENT) AS a1 WHERE a1.rn BETWEEN 10 and 20

 

 

 

--------------------------------------------------------------------

1. 標準的rownum分頁查詢使用方法：

    select *
      from (select c.*, rownum rn from content c)
     where rn >= 1
       and rn <= 5

    2. 但是如果, 加上order by addtime 排序則資料顯示不正確

    select *
      from (select c.*, rownum rn from content c order by addtime)
     where rn >= 1
       and rn <= 5

    解決方案，再加一層查詢，則可以解決，

    select *
      from (select rownum rn, t.*
              from (select title, addtime from content order by addtime desc) t)
     where rn >= 1
       and rn <= 5

 

    如果要考慮到效率的問題，上面的還可以最佳化成（主要兩者區別）

    select *
      from (select rownum rn, t.*
              from (select title, addtime from content order by addtime desc) t
             where rownum <= 10)
     where rn >= 3

 

----------------------------------------------------------------------

如果查詢結果需要分頁,從效率上講,下面的效率應該比較高(粉紅色表示需要根據實際情況填充的地方)

三類資料庫中進行分頁查詢的高效率方法(目前來看):

Oracle:   SELECT * FROM (       
             SELECT MY_TABLE.*,ROWNUM AS MY_ROWNUM FROM (     
            /** 括弧裡寫實際的需要查詢的SQL語句**/
           ) AS MYTABLE WHERE ROWNUM <=200/**這裡是一頁中的最後一條記錄**/  
                /) WHERE MY_ROWNUM>=10 /**這裡是一頁中的第一條記錄**/

SQLServer:  SELECT * FROM (
                             SELECT TOP 頁面容量 * FROM (
                                SELECT TOP 頁面容量*當前頁碼 * FROM
                                    表 WHERE 條件 ORDER BY 欄位A ASC
                                     )AS  TEMPTABLE1 ORDER BY 欄位A DESC
                                      ) AS TEMPTABLE2 ORDER BY 欄位A ASC

MYSQL:     SELECT 語句 LIMIT 頁面的第一條記錄-1,頁面容量