分页查询格式1
在查询的最外层控制分页的最小值和最大值。查询语句如下:
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM emp where emp.sal <3000) A
)
WHERE RN BETWEEN 1 AND 5 ;
分页查询格式2
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM emp where emp.sal <3000) A
WHERE ROWNUM <= 5
)
WHERE RN >= 1 ;
分页查询格式3
考虑到多表联合的情况,如果不介意在系统中使用HINT的话,可以将分页的查询语句改写为:
SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21