ORACLE中ROWNUM的机制和注意细节(避坑

问题背景

mybatis对接oracle数据库中会用ROWNUM做分页处理。
形如如下sql

SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( SELECT *
FROM YOUR_TABLE ) TMP WHERE ROWNUM <= ?) WHERE ROW_ID > ?

简单说，ROWNUM就是一个对查找结果分配行号的伪列。

问题

分别执行如下两个sql

select rownum,id,name from student where rownum=1;

和

select rownum,id,name from student where rownum=2;

会发现1可以查到数据，但是2不行。

原因和机制

ROWNUM的工作机制

1. 行生成：SQL查询语句生成结果集的所有行。
2. 行号分配：在结果集中，从第一行开始依次赋予ROWNUM值。
3. 条件过滤：在结果集上应用WHERE条件。

当WHERE ROWNUM = 1时，Oracle会遵循以下步骤：

首先选择第一行，并将ROWNUM设置为1。
然后检查WHERE条件，发现ROWNUM等于1，因此返回这行数据。

当WHERE ROWNUM = 2时：

Oracle首先选择第一行，并将ROWNUM设置为1。
检查WHERE条件，发现ROWNUM不等于2，因此丢弃这行数据。
Oracle继续尝试下一行，将其ROWNUM设置为1，再次检查WHERE条件，仍然不满足，继续丢弃。
因为ROWNUM始终从1开始，所以永远不会有ROWNUM等于2的行被选中。

如何查找ROWNUM = 2的记录

需将查找结果作为子查询再在外面包一层，如下

SELECT * FROM (
    SELECT ROWNUM AS RN, ID, NAME 
    FROM STUDENT
) 
WHERE RN = 2;