Oracle、MYSQL、sqlserver和DB2分頁查詢

　　DB:

　　DB分頁查詢

　　SELECT * FROM (Select 字段字段字段rownumber() over(ORDER BY 排序用的列名 ASC) AS rn from 表名) AS a WHERE arn BETWEEN AND

　　以上表示提取第到的紀錄

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

　　如果Order By 的字段有重復的值那一定要把此字段放到 over()中

　　select * from ( select ROW_NUMBER() OVER(ORDER BY DOC_UUID DESC) AS ROWNUM DOC_UUID DOC_DISPATCHORG       DOC_SIGNER DOC_TITLE    from DT_DOCUMENT  ) a  where ROWNUM > and ROWNUM <=

　　增加行號不排序

　　select * from ( select ROW_NUMBER() OVER() AS ROWNUMt*  from DT_DOCUMENT  t ) a

　　增加行號按某列排序

　　select * from ( select ROW_NUMBER() OVER( ORDER BY DOC_UUID DESC ) AS ROWNUMt*  from DT_DOCUMENT  t ) a

　　Mysql:

　　最簡單

　　select * from table limit startpageNum

　　比如從取個數據

　　select * from table limit

　　Oracle

　　select * from (select rownumname from table where rownum <=endIndex ) where rownum > startIndex

　　例如從表Sys_option(主鍵為sys_id)中從條記錄還是檢索條記錄語句如下

　　SELECT *

　　FROM (SELECT ROWNUM Rt* From Sys_option where rownum < ) t

　　Where tR >=

　　sql server:

　　例如從表Sys_option(主鍵為sys_id)中從條記錄還是檢索條記錄語句如下

　　SELECT *

　　FROM (SELECT ROWNUM Rt* From Sys_option where rownum < ) t

　　Where tR >=

From:http://tw.wingwit.com/Article/program/Oracle/201311/18958.html