oracle分页查询
1、表 STUDENT
ID NAME CREATE_TIME
01 dong 2014-03-03 12:13:14
02 xiao 2014-03-04 12:13:14
03 wang 2014-03-05 12:13:14
2、基础查询语句
SELECT ID, NAME, CREATE_TIME
FROM STUDENT
WHERE CREATE_TIME > TO_DATE('2014-03-02 12:13:14', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY CREATE_TIME
//这里可以按你想要的规则筛选和排序
3、查询总条数SQL语句
SELECT COUNT(*) FROM (基础查询语句)
//查询出总条数后,可以根据分页大小,算出分页数
4、分页查询SQL语句(效率高)
SELECT * FROM
(
SELECT TA.*, ROWNUM RN FROM (基础查询语句) TA
WHERE ROWNUM <= (page_num * one_page_size)
) TB
WHERE TB.RN > ((page_num-1) * one_page_size )
//page_num从1开始
//此种方法查询效率较高
5、分页查询SQL语句(效率低)
SELECT * FROM
(
SELECT TA.*, ROWNUM RN FROM (基础查询语句) TA
) TB
WHERE TB.RN > ((page_num-1) * one_page_size )
AND TB.RN <= (page_num * one_page_size)
//page_num从1开始
//此种方法查询效率较低