ORACLE分页方法

bruce198

1、rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数。
2、rownum不能以任何基表的名称作为前缀。

--自定义表phone
Name  Type         Nullable
----- ------------ --------
ID    NUMBER              
NAME  VARCHAR2(20) Y      
PHONE VARCHAR2(20) Y      
ZIP   NUMBER       Y      
--插入数据
        ID NAME                 PHONE                       ZIP
---------- -------------------- -------------------- ----------
         1 e                                        
         2 c                                                101
         3 a                                                102
         4 o                                                103
         5 f                                                103
         6 z                                                103
         7 p                                                103
         8 m                                                104
         9 g                                                102
--显示所有记录,按zip和name排序
select rownum, p.zip, p.name from phone p order by zip, name;
    ROWNUM        ZIP NAME                 ROWID
---------- ---------- -------------------- ------------------
         2        101 c                    AAAH3vAABAAAPD6AAB
         3        102 a                    AAAH3vAABAAAPD6AAC
         9        102 g                    AAAH3vAABAAAPD6AAI
         5        103 f                    AAAH3vAABAAAPD6AAE
         4        103 o                    AAAH3vAABAAAPD6AAD
         7        103 p                    AAAH3vAABAAAPD6AAG
         6        103 z                    AAAH3vAABAAAPD6AAF
         8        104 m                    AAAH3vAABAAAPD6AAH
         1            e                    AAAH3vAABAAAPD6AAA
可见,rownum由系统按照记录入库时的顺序排的号,不可以改变,rowid也是的,如果要重排rownum,需要这样...
--按zip和name排序后,显示前5条记录(对照上面的结果),外层的rownum重排了,是我们需要的顺序
select rownum, p.*
  from (select zip, name from phone group by zip, name) p
 where rownum <= 5;
或者...
select rownum, p.*
  from (select t.zip, t.name from phone t order by t.zip, t.name) p
 where rownum <= 5;
    ROWNUM        ZIP NAME
---------- ---------- --------------------
         1        101 c
         2        102 a
         3        102 g
         4        103 f
         5        103 o

--按zip和name排序后,显示第5-第9条记录(对照上上面的结果)
select *
  from (select rownum row_id, p.*
          from (select zip, name from phone group by zip, name) p)
 where row_id between 5 and 9;
 
    ROW_ID        ZIP NAME
---------- ---------- --------------------
         5        103 o
         6        103 p
         7        103 z
         8        104 m
         9            e