同事发来一条 SQL，说订单列表翻到第二页是空的，第一页正常。语句大概是 select * from orders where rownum > 10 and rownum <= 20，执行计划没报错，返回 0 行。

原因在 rownum 的赋值时机：它在 where 过滤之前逐行给号。第一行进来拿到 1，被 rownum > 10 挡掉；第二行进来还是 1，又被挡掉。永远没有行能跨过 10 这个坎。

能直接写在 where 里的只有两种

  • rownum = 1，或者 rownum <= n，取前 n 行。
  • rownum < n，取前 n-1 行，注意是小于号，不是小于等于。

要取「大于」就得先把序号落成普通列，再在外层过滤：

select * from (select t.*, rownum rn from orders t where rownum <= 20) where rn > 10;

内层先取 20 行并编号，rn 这时是真实列，外层再筛 11 到 20，结果才对。内层少写 rownum <= 20，Oracle 会先把符合条件的数据全部读出来再截断，大表上慢得很明显。

order by 和 rownum 的顺序容易搞反

rownum 在排序之前赋值。下面这句的序号是按读取顺序给的，排完序序号就对不上行了：

select rownum, id, amount from orders order by amount desc;

想让编号跟着排序走，得先排序再编号，套一层子查询。分页同理，排序必须发生在取 rownum 之前，否则每页会出现重复行或者漏行。线上翻页时偶尔看到同一条记录出现在两页，多半就是这个原因。

12c 之后有更直的写法

select * from orders order by id offset 10 rows fetch next 10 rows only;

11g 及更早的库没有这个语法，只能靠 rownum 套子查询。维护老系统时看到 rn、rn1 这类别名，基本都是分页留下的痕迹。

还有一点，rownum 和 row_number() over() 不是一回事。前者是取到行的顺序号，随执行计划变化；后者按窗口内排序编号，结果稳定。要稳定分页和排名，用 row_number 更靠谱。