[oracle]使用rownum进行简单分页查询

最早接触的数据库是mysql，mysql里面有个独有的limit功能特别适合少量数据的分页查询。

现如今使用oracle数据库后，发现它并没有limit这个功能，上网搜了许久，才知道有一个可以用于分页的字段rownum。

话不多说，举例说明。

create table testData(
       id number(5) primary key,
       content varchar2(50)
);

insert into testData values(1,'这是测试数据1');
insert into testData values(2,'这是测试数据2');
insert into testData values(3,'这是测试数据3');
insert into testData values(4,'这是测试数据4');
insert into testData values(5,'这是测试数据5');
insert into testData values(6,'这是测试数据6');
insert into testData values(7,'这是测试数据7');
insert into testData values(8,'这是测试数据8');
insert into testData values(9,'这是测试数据9');
insert into testData values(10,'这是测试数据10');
insert into testData values(11,'这是测试数据11');
insert into testData values(12,'这是测试数据12');
insert into testData values(13,'这是测试数据13');
insert into testData values(14,'这是测试数据14');
insert into testData values(15,'这是测试数据15');
insert into testData values(16,'这是测试数据16');
insert into testData values(17,'这是测试数据17');
insert into testData values(18,'这是测试数据18');
insert into testData values(19,'这是测试数据19');
insert into testData values(20,'这是测试数据20');
insert into testData values(21,'这是测试数据21');
insert into testData values(22,'这是测试数据22');
insert into testData values(23,'这是测试数据23');
insert into testData values(24,'这是测试数据24');
insert into testData values(25,'这是测试数据25');
insert into testData values(26,'这是测试数据26');
insert into testData values(27,'这是测试数据27');
insert into testData values(28,'这是测试数据28');
insert into testData values(29,'这是测试数据29');
insert into testData values(30,'这是测试数据30');
commit;

select * from testData;

这里随便新建了一个表，插入了30条数据来演示。

1.rownum最常遇到的问题

关于这个rownum，很多人刚接触的时候很容易当成limit来用，结果往往使用>、>=、=、between…and这些比较符号的时候发现查出来的结果集不正确，比如如下写法，是想查出来第11条及以后的数据，为什么结果集是空的？

select rownum,t.* from testData t where rownum>10;

而使用的比较符号是<、<=、!=时，往往又能成功查询出来数据，比如查前10条数据是可以成功查出来的嘛。

select rownum,t.* from testData t where rownum<=10;

这里就需要详细讲解一下rownum是什么东西，了解之后才能知道自己错在哪。

2.rownum详解

2.1.rownum是相对位置

rownum实际是一个伪列，伪列指的是在物理上这个列并不存在，只是在查询时才构造出来。伪列通常是自由分配的，用户无法执行修改等操作。

如何来理解这个伪列？举一个排队的例子。

假如有很多人去现场买演唱会的票，大家都遵守纪律排队，排成非常长的一条直线。那么rownum指的就是相对位置，排头的就是第1，即rownum=1，排第2的就是rownum=2。假如原本排第1的人离开了，原本排第2的就变成了第1，他的rownum也变成了1。所以可以理解成，rownum就是排队时的相对位置，而不像主键一样作为行的唯一标识。

假设有一个管理员在现场管理排队，且目前这个队列一共有30人，我们设置条件rownum<=10的时候，实际就相当于管理员从排头的第1依次往后问：“你是排在前10的吗？”，排第1的当然满足，所以留下。管理员问后面一个人(第2位)“你是排在前10的吗？”，也满足，所以第二个人留下来了…一直问到11个人时候，不满足了，所以管理员把第11个人踢出了队列，原本的第12人就成了此时的第11人，管理员问同样的问题，这个人不满足条件，所以又被踢出去…一直到最后，只有前面10个人留了下来，后面20人
都被踢出去了，所以这10人就是被成功筛选有效的数据。

2.2.条件中rownum范围必须包含1

还有一点很重要，条件中rownum的范围必须包含1。

如果不包含1会出现什么情况？以rownum>10为例，还是30人队列。咋一看这个条件是把排头前10人踢出去，留下后面的20人。但管理员不是这样
判断的，他还是从排第1的人开始问"你是排在第10位之后的吗？"，第1的人不满足，所以他被踢出去了，原本的第2位此时成了第1位(他的rownum由原本的2变成了1)，管理员又继续问同样的问题，这个人也不满足条件又被踢了出去。管理员依次往后问，每个人都依次变成了第1，但因为排第1的总是不满足条件被踢出去，所以管理员问完了30个但没有一个人符合条件的，全被踢出了队列，剩下的人数当然是0。

下面这个写法同样也能获取排前10的效果，用上面排队例子的理解就能知道为什么这个写法和rownum<=10效果一致了

select rownum,t.* from testData t where rownum!=11;

这也是为什么使用的比较符号是<、<=、!=时往往有数据，而>、>=、=、between…and往往没有数据，主要还是使用者不明白rownum的原理。

3.如何使用rownum有效查询

如果是查询前n条数据的话，直接在条件里使用rownum<=n就行了。如果是查询第n到第m条数据的话，不能使用rownum>=n and rownum<=m这种写法，必须要使用嵌套查询，其中要把rownum变成结果集的真列。

例如查询第11-20条数据

select * from
(
	select rownum as rn,t.* from testData t
	where rownum<=20
) t
where t.rn>=11;

可以看到内层查询结果为数据集t，原本的rownum变成了数据集t里的真列rn，那么在外层查询里面就可以使用rn来进行判断了。

不理解上面嵌套查询代码的话，还是用排队的方式来解释，执行内层查询时，管理员还是和以往一样的判断，这样把前20人保留了下来，把后10个不满足条件的踢了出去。
但是保留下这20人之后，管理员给每个人发放了一个小牌牌(结果集t的字段rn)，排第1的小牌牌写着1(rn=1)，排第2的小牌牌写着2(rn=2)，以此类推。
然后管理员又来判断一次，从排头开始依次往后问，但这时候不再是根据排队位置问，而是根据小牌牌的位置问。管理员问排第1的"你满足rn>10吗？"，不满足就踢了出去，
依次往后问，最后留下的人的小牌牌分别是11-20的，其实在内层查询的结果集t里，字段rn不再是作为相对位置，反而具有唯一性。

当知道怎么使用rownum来嵌套查询后，达成的功能和mysql的limit已经很像了，分页查询也不是什么难事了。