oracle分页(使用rownum[伪列])

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as tudou

SQL> select * from temp1;

NAME AGE
------------------- ---
土土 22
z壮 23
z 25
a 28
奇才 29
才子、 12
二飘落 14
脾用 18
fdffs 18
ppppp 18
大规模 13

11 rows selected

SQL> select * from temp1 where rownum<4;

NAME AGE
------------------- ---
土土 22
z壮 23
z 25

SQL> select * from (select * from temp1 where rownum<=3*4) where age not in(select age from temp1 where rownum<=(3-1)*4);

NAME AGE
------------------- ---
大规模 13

SQL> select * from (select * from temp1 where rownum<=2*4) where age not in(select age from temp1 where rownum<=(2-1)*4);

NAME AGE
------------------- ---
奇才 29
才子、 12
二飘落 14
脾用 18

SQL>

ORACLE中使用rownum[伪列]进行分页：

rownum分页
select *　from(select m.*,ROWNUM　rn from(select * from tb)m) where rn between 11 and 20

select *　from(select m.*,ROWNUM　rn from(select * from tb)m where ROWNUM<=20) where rn >=20

第二种算法效率比第一种高...