rownum的使用-分页

时间:2021-12-13 16:36:56

oracle分页显示方法

一、使用rownum分页显示方式

方式1:
SELECT *
  FROM (SELECT ROWNUM r, a.* FROM b$i_exch_info a WHERE ROWNUM <= 10)
 WHERE r >= 5;

方式2:
SELECT *
  FROM (SELECT ROWNUM r, a.* FROM b$i_exch_info a)
 WHERE r BETWEEN 5 AND 10;
 
方式3:
SELECT * FROM b$i_exch_info WHERE ROWNUM <= 10 MINUS
SELECT * FROM b$i_exch_info WHERE ROWNUM < 5;

二、使用分析函数row_number分页显示
SELECT *
  FROM (SELECT e.*, row_number() over(ORDER BY g3e_fid) r
          FROM b$i_exch_info e) a
 WHERE a.r >= 5
   AND a.r <= 10;

注意事项

1.

--10g及10g之后才可以使用rownum=1
SELECT * FROM user_objects
  WHERE /*object_id <100
  AND*/ ROWNUM = 1;

--之前的版本  
SELECT * FROM user_objects
  WHERE object_id <100
  AND ROWNUM <= 1;
 
2.
rownum采用大于号>时 其值必须小于1,否则查询无结果
SELECT * FROM user_objects
  WHERE ROWNUM >1;
 
>= 时其值必须小于或等于1,否则查询无结果
SELECT * FROM user_objects
  WHERE ROWNUM >=2;

= 时其只能等于1,否则查询无结果
SELECT * FROM user_objects
  WHERE ROWNUM =2;

3.ROWNUM 和Order BY
在使用ROWNUM 时,只有当Order By 的字段是主键时,查询结果才会先排序再计算ROWNUM:
g3e_ano是主键
SELECT g3e_ano,g3e_username FROM g3e_attribute WHERE ROWNUM <= 5 ORDER BY g3e_ano;
1        备注
1002    组件序号
1008    组件序号
1009    组件序号
1010    组件序号

--以下查询因为ORDER BY的g3e_username不是主键,所以执行时是先线取出该表的6条数据,再对g3e_username排序
SELECT g3e_ano,g3e_username FROM g3e_attribute WHERE ROWNUM <= 5 ORDER BY g3e_username;
111003    设施特征唯一号
113203    设施特征唯一号
50110      设施特征唯一号
1510103    设施特征唯一号
112003    设施特征唯一号

--如果需要对非主键字段先排序再去取前n 条数据,可以通过子查询的方式实现:
select g3e_ano, g3e_username
  from (select g3e_ano, g3e_username
          from g3e_attribute
         order by g3e_username)
 where rownum <= 5;

--每页按10条记录输出(如果被排序的字段有重复值,使用rownum会出现一个问题):
--观察下面两个语句的输出结果会发现其中55461451和55461209是在两个查询中都出现了。而fid在表中都是唯一记录的,
--说明这个输出结果是错误的
错误原因:SORT (ORDER BY STOPKEY)这种快速排序方法由于是根据数据分组来选择数据的,不是根据整个表的数据进行排序,所以N
值不同,数据的分组也不同,导致结果在数据的排序字段值都相等时,输出结果的顺序就会因为N 值不同而不同。

SELECT *
  FROM (SELECT ROWNUM r, a.*
          FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name) a
         WHERE ROWNUM <= 10)
 WHERE r >= 1;
1       王家宅    55461079
2       王家宅    55461206
3       王家宅    55461207
4       王家宅    55461253
5       王家宅    55461246
6       王家宅    55461209
7       王家宅    55461783
8       王家宅    55461646
9       王家宅    55461586
10     王家宅    55461451
SELECT *
  FROM (SELECT ROWNUM r, a.*
          FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name) a
         WHERE ROWNUM <= 20)
 WHERE r >= 11;
11       王家宅    56990485
12       王家宅    56990368
13       王家宅    56981862
14       王家宅    56981861
15       王家宅    56981807
16       王家宅    56981806
17       王家宅    56981801
18       王家宅    55461646
19       王家宅    55461451
20       王家宅    55461209

解决办法:
1、让查询计划避免“SORT (ORDER BY STOPKEY)”,采用“SORT (ORDER BY)”,使数
据排序不受ROWNUM 的影响。但这样会使所有数据都做排序:
SELECT *
  FROM (SELECT a.*, ROWNUM r
          FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name) a)
 WHERE r <= 10
   AND r >= 1;

SELECT *
  FROM (SELECT a.*, ROWNUM r
          FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name) a)
 WHERE r <= 20
   AND r >= 11;
2、在排序时,加上一个或多个字段(如主键字段、ROWID),使排序结果具有唯一性:
SELECT *
  FROM (SELECT ROWNUM r, a.*
          FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name,g3e_fid) a
         WHERE ROWNUM <= 10)
 WHERE r >= 1;
 
SELECT *
  FROM (SELECT ROWNUM r, a.*
          FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name,g3e_fid) a
         WHERE ROWNUM <= 20)
 WHERE r >= 11;
3、对排序字段建立索引,并强制使用索引。这样就能利用索引已经建立好的排序结果:
CREATE INDEX idx_b$l_interest_info_name ON b$l_interest_info(name);
ALTER INDEX idx_b$l_interest_info_name REBUILD;

SELECT *
  FROM (SELECT ROWNUM r, a.*
          FROM (SELECT /*+index(a idx_b$l_interest_info_name)*/
                 name, g3e_fid
                  FROM b$l_interest_info a
                 WHERE a.name IS NOT NULL
                 ORDER BY name) a
         WHERE ROWNUM <= 10)
 WHERE r >= 1;
 
SELECT *
  FROM (SELECT ROWNUM r, b.*
          FROM (SELECT /*+index(a idx_b$l_interest_info_name)*/
                 a.name, a.g3e_fid
                  FROM b$l_interest_info a
                 WHERE a.name IS NOT NULL
                 ORDER BY a.name) b
         WHERE ROWNUM <= 20)
 WHERE r >= 11;