日期:2014-05-16 浏览次数:20457 次
1.数据表结构
表名
Authoring
字段
authoringId NUMBER PRIMARY KEY,
name?VARCHAR(50) NOT NULL,
startDate Date
?
2.分页:以startDate逆序分页
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的值
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;
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条数据用于判断是否存在下一页。
?