ORACLE分页方法

1.数据表结构

表名

Authoring

字段

authoringId NUMBER PRIMARY KEY，

name VARCHAR(50) NOT NULL,

startDate Date

 

2.分页：以startDate逆序分页

• 方法1

SELECT *
  FROM (SELECT a.*, row_number() OVER(ORDER BY a.STARTDATE DESC) rownumber
          FROM AUTHORING a) tmp
 WHERE tmp.rownumber between 1 and 5;

 

SELECT *
  FROM (SELECT tmp.*, rownum rownumber
          FROM (SELECT a.* FROM AUTHORING a ORDER BY STARTDATE DESC) tmp) rslt
 WHERE rslt.rownumber between 1 and 5;

 

注: ORDER BY 是对where过滤之后的结果在缓存内进行order，

     根据rownum的原理，order之前rownum的值已经确定，

     所以分页时需先order by 然后再取rownum的值

• 方法2

select *
  from (SELECT tmp.*, rownum rownumber
          FROM (SELECT a.* FROM AUTHORING a ORDER BY STARTDATE DESC) tmp
         where rownum <= 5) rslt
 WHERE rslt.rownumber >= 1;

• 方法3

SELECT tmp.*
FROM (SELECT a.* FROM AUTHORING a ORDER BY STARTDATE DESC, Authoringid) tmp
WHERE rownum <= 6;

(第6条记录的authoringId=1076328)

 

SELECT tmp.*
FROM (
     SELECT a.STARTDATE, a.Authoringid, a.name
     FROM AUTHORING a
     WHERE a.startdate < (SELECT a.startdate FROM AUTHORING a WHERE a.AUTHORINGID = 1076328)
     UNION
     SELECT a.STARTDATE, a.Authoringid, a.name 
     FROM AUTHORING a W
     HERE a.startdate = (SELECT a.startdate FROM AUTHORING a WHERE a.AUTHORINGID = 1076328) and a.authoringid >= 1076328
     ORDER BY STARTDATE DESC, Authoringid
) tmp
WHERE rownum <= 6;

 

此方法不能准备定位于某一页，只支持previous和next，每次需要多取1条数据用于判断是否存在下一页。