分页查询在Oracle和MqSQL上的实现有很大区别,首先介绍在MySQL上的实现:
MySQL中的分页查询
1. limit 是一个MySQL"方言"
2. 语法:limit 每页查询的条数 offset 开始的索引,
简化版:limit 开始的索引,每页查询的条数;
3. 公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数
-- 每页显示3条记录
SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页
Oracle中的分页查询
Oracle的分页是由行号 rownum 实现的,rownum 是一个Oracle"方言",分页实现起来比较复杂,不过有一个固定的模式。
rownum: 当我们做 select 操作时,每查询出一行记录,就会在该行上加上一个行号,行号从1开始,依次递增,不能跳着走。
以scott用户下的表为例:
-- 普通查询,rownum完全可以省略,它是自动加上去的,这里只是方便讲解才加上去的
select rownum, e.* from emp e;
如果用order by排序之后,rownum 会乱,即排序操作会影响 rownum 的顺序,因为 rownum 是伴随select后的结果集产生的伪列:
-- 用order by排序之后
select rownum, e.* from emp e order by e.sal;
这就带来一个问题,如果我们想用薪水排序然后再分页怎么办哪?可以这样做,我们先排序再加行号:
-- 先排序再加行号
SELECT rownum, t.* FROM
(select rownum, e.* from emp e order by e.sal) t;
下面我们来分页,假如我们每5个记录一页:
-- 第一页这样写没问题
SELECT rownum, t.* FROM
(select rownum, e.* from emp e order by e.sal) t
where rownum<6;
-- 第二页发现结果为空??
SELECT rownum, t.* FROM
(select rownum, e.* from emp e order by e.sal) t
where rownum>5 and rownum <11;
因为 rownum 是对结果集加的一个伪列,即先查到结果集之后再加上去的一个列 (先要有结果集)。简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的,总而言之就是 rownum 不能大于一个正数,那怎么解决这个问题?我们的思路是在 rownum<11 的基础上筛选出 rownum>5 的部分,我们再加一层select,然后给里面的rownum起个别名,让他变成真的一列,然后进行筛选:
-- 间接写>,再加一层select
select * from(
SELECT rownum rn, t.* FROM
(select rownum, e.* from emp e order by e.sal) t
where rownum <11
) where rn>5;
所以上面是一种分页查询固定的模式,即:
select * from(
select...
where rownum <11
) where rn>5;