http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
Consider a query with this structure:
select ..., ROWNUM from t where <where clause> group by <columns> having <having clause> order by <columns>;
Think of it as being processed in this order:
1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. GROUP BY is applied.
5. HAVING is applied.
6. ORDER BY is applied.
建表
create table t as select mod(level,5) id, trunc(dbms_random.value(1,100)) data from dual connect by level <= 100;
查询第40条到50条
select * from (select a.*, rownum rnum from (select id, data from t order by id) a where rownum <= 50) where rnum >= 40;