Oracle / SQL：为什么查询“SELECT * FROM records WHERE rownum> = 5 AND rownum

Why does the following query return 'zero' records:

为什么以下查询返回“零”记录：

SELECT * FROM records WHERE rownum >= 5 AND rownum <= 10

     OR

SELECT * FROM records WHERE rownum >= 5

Whereas the following query return proper records:

以下查询返回正确的记录：

SELECT * FROM records WHERE rownum <= 15

Regards,
- Ashish

问候， - Ashish

3 个解决方案

#1

In Oracle, Rownum values are assigned after the filtering stage of the query - they are not rows of the table, they are rows of the query result set.

在Oracle中，Rownum值是在查询的过滤阶段之后分配的 - 它们不是表的行，它们是查询结果集的行。

So the first row that comes back will always be given rownum 1, the second row that comes back rownum 2, etc.

所以返回的第一行将始终给出rownum 1，第二行返回rownum 2，等等。

The rownum value is only incremented after it is assigned, so any query like

rownum值仅在分配后递增，因此任何查询都可以

select * from t where ROWNUM > 1

will never return any results. This query says 'I dont want to see the first row that gets returned to me, only the ones after that' which is sortof a paradox so nothing gets returned.

将永远不会返回任何结果。这个查询说'我不想看到第一行返回给我，只有之后的那一行'，这是一个悖论，因此没有任何东西被返回。

See Ask Tom:On ROWNUM and Limiting Results for more details.

有关详细信息，请参阅询问Tom：在ROWNUM和限制结果。

#2

ROWNUM is a pseudocolumn and it's value will not be available at the time of query execution. So you can't filter your results based on this column. With that said, there are work arounds to mitigate it. Please see below (it is not recommended to use if your result is very huge).

ROWNUM是一个伪列，它的值在查询执行时将不可用。因此，您无法根据此列过滤结果。话虽如此，有一些工作可以减轻它。请参阅下文（如果结果非常大，建议不要使用）。

Select a.* From 
(
  Select COL1, COL2, ROWNUM RowNumber From MyTable
) a
Where
   RowNumber = 5;

#3

Alternative is to use MINUS

另一种方法是使用MINUS

SELECT * FROM records 
WHERE   ROWNUM <= 10
minus SELECT * FROM records 
WHERE   ROWNUM <= 5

this will filter out non-unique values so you better be selecting id.

这将过滤掉非唯一值，因此您最好选择ID。

Hope this saves you some time.

希望这能为您节省一些时间。

#1