Oracle SQL实现分页查询

xiaoxiao2021-02-28 62

Oracle SQL分页查询

1、表结构

SQL> desc test; 名称是否为空? 类型 --------------------- -------- --------------- ID NOT NULL NUMBER(8) USERNAME VARCHAR2(32) PASSWORD VARCHAR2(32) AGE NUMBER(3) BIRTHDAY DATE ADDRESS VARCHAR2(40)

2、表记录条数

SQL> select count(*) from test; COUNT(*) ---------- 5000001

3、表中部分数据

SQL> select * from test where rownum <10; ID USERNAME PASSWORD AGE BIRTHDAY ADDRESS ---------- ---------------------- ------------------------------ -------------- ---------- 325610 user325610 password325610 10 15-10月-15 The_Earth 325611 user325611 password325611 11 15-10月-15 The_Earth 325612 user325612 password325612 12 15-10月-15 The_Earth 325613 user325613 password325613 13 15-10月-15 The_Earth 325614 user325614 password325614 14 15-10月-15 The_Earth 325615 user325615 password325615 15 15-10月-15 The_Earth 325616 user325616 password325616 16 15-10月-15 The_Earth 325617 user325617 password325617 17 15-10月-15 The_Earth 325618 user325618 password325618 18 15-10月-15 The_Earth

4、进行分页

1、常用方式

这种方式比较好理解，而且内层查询效率高，整体查询效率较稳定。

SQL> select * 2 from (select row_.*, rownum rownum_ 3 from (select * 4 from test 5 order by id asc) row_ 6 where rownum <= 20) 7 where rownum_ >= 10;

或者

SQL> select * 2 from (select test.*, rownum rownum_ from test where rownum <= 5000000 order by id asc) 3 where rownum_ >= 4999990;

查询结果

ID USERNAME PASSWORD AGE BIRTHDAY ADDRESS ---------- -------------------------------- -------------------------------- ---------- -------------- ----------- 4999989 user4999989 password4999989 69 15-10月-15 The_Earth 4999990 user4999990 password4999990 70 15-10月-15 The_Earth 4999991 user4999991 password4999991 71 15-10月-15 The_Earth 4999992 user4999992 password4999992 72 15-10月-15 The_Earth 4999993 user4999993 password4999993 73 15-10月-15 The_Earth 4999994 user4999994 password4999994 74 15-10月-15 The_Earth 4999995 user4999995 password4999995 75 15-10月-15 The_Earth 4999996 user4999996 password4999996 76 15-10月-15 The_Earth 4999997 user4999997 password4999997 77 15-10月-15 The_Earth 4999998 user4999998 password4999998 78 15-10月-15 The_Earth 4999999 user4999999 password4999999 79 15-10月-15 The_Earth 已选择11行。 1

2、第二种方式

采用这种方式，查询越靠后，效率越低，整体需要查询两次，而且逐条比较，不推荐使用。

SQL> select * 2 from test 3 where id not in 4 (select id from test 5 where rownum <= 20) 6 and rownum <= 10 order by id asc;

3、第三种方式

这种是采用minus函数取差集的方式获取，同样查询越靠后，效率越低

SQL> select * 2 from test 3 where rownum <= 20 4 minus 5 select * from test where rownum <= 10;