Oracle、MYSQL、sqlserver和DB2分页查询

来源：互联网发布：交互式动态流程图软件编辑：程序博客网时间：2024/04/26 08:27

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 ) 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 > 20 and ROWNUM <=30

　　增加行号，不排序

　　select * from ( select ROW_NUMBER() OVER() AS ROWNUM,t.* from DT_DOCUMENT t ) a

　　增加行号，按某列排序

　　select * from ( select ROW_NUMBER() OVER( ORDER BY DOC_UUID DESC ) AS ROWNUM,t.* from DT_DOCUMENT t ) a

　　Mysql:

　　最简单

　　select * from table limit start,pageNum

　　比如从10取20个数据

　　select * from table limit 10,20

　　Oracle：

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

　　例如从表Sys_option(主键为sys_id)中从10条记录还是检索20条记录，语句如下：

　　SELECT *

　　FROM (SELECT ROWNUM R,t1.* From Sys_option where rownum < 30 ) t2

　　Where t2.R >= 10

　　sql server:

　　例如从表Sys_option(主键为sys_id)中从10条记录还是检索20条记录，语句如下：

　　SELECT *

　　FROM (SELECT ROWNUM R,t1.* From Sys_option where rownum < 30 ) t2

　　Where t2.R >= 10

0 0