oracle 查询分页sql语句

//oracle 查询分页
sql语句1：

select * from (select A.*, rownum rn from (select * from student) A) where rn

between 1 and  2;

sql语句2（据说这种方法查询效率高，未验证）：

select * from (select A.*,rownum rn from (select * from student where s_id between 5 and 9) A

where rownum<=3) where rn>=1

 

 

SQL> select * from student;
 
S_ID                                                                             S_NAME                                             S_AGE
-------------------------------------------------------------------------------- -------------------------------------------------- -----
1                                                                                potter                                                26
2                                                                                add                                                  21
3                                                                                had                                               12
4                                                                                renee                                                 15
5                                                                                reday                                                 18
6                                                                                alisa                                                 19
7                                                                                胡总                                                  23
8                                                                                阿里山                                                29
9                                                                                扎西                                                  20
10                                                                               香格里拉                                              24
11                                                                               琪琪格                                                28
12                                                                               暗夜精灵                                              19
 
12 rows selected
 
SQL> select * from (select A.*, rownum rn from (select * from student) A) where rn between 2 and 4;
 
S_ID                                                                             S_NAME                                             S_AGE         RN
-------------------------------------------------------------------------------- -------------------------------------------------- ----- ----------
2                                                                                add                                                  21          2
3                                                                                had                                                12          3
4                                                                                renee                                                 15          4
 
SQL> select * from (select A.*, rownum rn from (select * from student) A) where rn between 1 and 5;
 
S_ID                                                                             S_NAME                                             S_AGE         RN
-------------------------------------------------------------------------------- -------------------------------------------------- ----- ----------
1                                                                                potter                                                26          1
2                                                                                add                                                  21          2
3                                                                                had                                                12          3
4                                                                                renee                                                 15          4
5                                                                                reday                                                 18          5
 
SQL> select * from (select A.*, rownum rn from (select * from student) A) where rn between 1 and 10;
 
S_ID                                                                             S_NAME                                             S_AGE         RN
-------------------------------------------------------------------------------- -------------------------------------------------- ----- ----------
1                                                                                potter                                                26          1
2                                                                                add                                                  21          2
3                                                                                had                                                12          3
4                                                                                renee                                                 15          4
5                                                                                reday                                                 18          5
6                                                                                alisa                                                 19          6
7                                                                                胡总                                                  23          7
8                                                                                阿里山                                                29          8
9                                                                                扎西                                                  20          9
10                                                                               香格里拉                                              24         10
 
10 rows selected

SQL> select * from (select A.*, rownum rn from (select * from student) A) where rn between 8 and 10;
 
S_ID                                                                             S_NAME                                             S_AGE         RN
-------------------------------------------------------------------------------- -------------------------------------------------- ----- ----------
8                                                                                阿里山                                                29          8
9                                                                                扎西                                                  20          9
10                                                                               香格里拉