1、使用两层嵌套
SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM (SELECT * FROM edw_t100_bal_all) A
WHERE ROWNUM <= 40)
WHERE RN >= 21;
2、使用between..and..
SELECT *
FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM edw_t100_bal_all) A)
WHERE RN between 21 and 40;
3、实践证明：方法1比方法2效率更高
SELECT /* + FIRST_ROWS */ *
FROM (SELECT A.*, ROWNUM RN
FROM (SELECT * FROM edw_t100_bal_all) A
WHERE ROWNUM <= 40)
WHERE RN >= 21;
以上语句会以更高的效率执行。