Oracle通用分页格式

对于没有order by语句的分页：

SELECT *
FROM (SELECT ROWNUM AS rowno, t.*
          FROM DONORINFO t
          WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')
          AND TO_DATE ('20060731', 'yyyymmdd')
          AND ROWNUM <= page*size) table_alias
WHERE table_alias.rowno > (page-1)*size;    

有order by语句的分页

SELECT *
FROM (SELECT ROWNUM AS rowno,r.*
           FROM(SELECT * FROM DONORINFO t
                    WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')
                    AND TO_DATE ('20060731', 'yyyymmdd')
                    ORDER BY t.BIRTHDAY desc
                   ) r
           where ROWNUM <= page*size 
          ) table_alias
WHERE table_alias.rowno > (page-1)*size;

另外我们也可以使用row_number() over函数：

select * 
from(select d.*,row_number() over(order by d.BIRTHDAY) as rownumber 
       from DONORINFO d
       WHERE d.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')
        AND TO_DATE ('20060731', 'yyyymmdd')
 ) p 
where p.rownumber BETWEEN size*(page-1)+1 AND page*size;

转载于:https://www.cnblogs.com/523823-wu/p/10498607.html