熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle、MYSQL、sqlserver和DB2分頁查詢

2013-11-13 22:23:21  來源: Oracle 

  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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.