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

在Oracle数据库中，ROWNUM是一个伪列，它为结果集的行返回唯一的数字。ROWNUM通常用于限制查询返回的结果集数量，或者进行分页查询。

需要注意的是，ROWNUM是在结果集形成之后才赋值的，这就意味着如果你在WHERE子句中使用ROWNUM来过滤结果，那么ROWNUM是在结果返回后才进行标记的，这可能会导致你期望的结果没有被筛选出来。

例如，以下查询将永远不会返回结果：




SELECT * FROM employees WHERE ROWNUM > 10;

因为ROWNUM是在结果集形成之后才赋值的，所以在形成结果集之前，Oracle会先给所有记录赋予ROWNUM值，此时所有记录的ROWNUM值都是1，因此WHERE子句条件ROWNUM > 10不会有任何记录满足。

为了解决这个问题，可以使用子查询：




SELECT * FROM (SELECT * FROM employees) WHERE ROWNUM <= 10;

在这个子查询中，首先从employees表中选择所有记录，然后外层查询根据ROWNUM来限制结果集的大小。

另外，ROWNUM是一个从1开始的唯一数字，如果你想要获取第10行到第20行的记录，你可以这样写：




SELECT * FROM (
  SELECT a.*, ROWNUM rnum FROM (SELECT * FROM employees ORDER BY employee_id) a
  WHERE ROWNUM <= 20
) WHERE rnum >= 10;

在这个查询中，首先根据employee_id对employees表进行排序，然后给排序后的结果集标上ROWNUM，最后在外层查询中筛选出rnum介于10到20之间的记录。

总结：