一、背景
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结合使用)。