Oracle实现limit

一、背景
oracle数据库不支持mysql、postgresql的limit功能，但是可以通过rownum（oracle系统顺序分配，为查询返回的行从1开始编号）来限制返回结果集的行数，达到同样的效果。


二、用法
-- 查询前10行记录
SELECT ROWNUM, t1.* FROM XXX t1 WHERE ROWNUM <= 10;

-- 查询从11行到20行的记录
SELECT * FROM XXX WHERE ROWNUM < 21 MINUS SELECT * FROM XXX WHERE ROWNUM < 11;
或者
SELECT * FROM (SELECT ROWNUM NO, t1.* FROM XXX t1) t2 WHERE t2.NO > 10 AND t2.NO < 21;

注意：select * from XXX where rownum > 10 and rownum < 21 会返回空结果集。


三、注意
1）select rownum from XXX where rownum != 10 和 select rownum from XXX where rownum < 10 返回的结果集是一样的，都是前9条数据。

rownum=9后，下一条记录的rownum=10，不符合!=10的条件，记录去除，后续记录补上，永远为10，不符合条件。

2）rownum >1 或 rownum=2等总是返回空结果集。

rownum是在查询到结果后加上的，总是从1开始，因此第一条记录永远不满足，后续记录不断往上补，不断的不成立，就返回了空结果集。

3）rownum不可以以任何表的名称作为前缀， 因此子查询中的rownum必须起别名。

select * from (select rownum no, t1.* from XXX t1) t2 where no>2;

4）rownum和排序

由于Oracle中的rownum是在取数据的时候产生的序号，所以
SELECT update_time FROM XXX WHERE ROWNUM = 1 ORDER BY update_time DESC; 不会返回根据更新时间倒序后取第一条。
正确的写法是：SELECT * FROM (SELECT update_time FROM XXX ORDER BY update_time DESC) t1 WHERE ROWNUM = 1;

5）rowid 与 rownum

①rowid是数据的详细地址（基于base64编码，共18位，分4部分），通过rowid，oracle可以快速的定位某行具体的数据的位置。可以分为物理rowid和逻辑rowid两种。普通堆表中的rowid是物理rowid，索引组织表(IOT)的rowid是逻辑rowid。oracle还提供了一种urowid的数据类型，同时支持物理和逻辑rowid。
②rowid与rownum虽然都被称为伪列，但有很大差异。rownum是根据sql查询出的结果给每行分配一个逻辑编号；而rowid是物理结构上的，在每条记录insert到数据库中时生成，查询的时候必须起别名，不然不会显示。
③rownum常用于分页；rowid用于处理一张表中的重复记录（主要与max、min结合使用）。