Oracle/SQL:为什么查询"SELECT * FROM records where rownum> = 5 AND rownum< = 10" -返回零行

问题描述

以下查询为什么返回零"记录:

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

     OR

SELECT * FROM records WHERE rownum >= 5 

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

SELECT * FROM records WHERE rownum <= 15

关于，
-Ashish

解决方案

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

因此，返回的第一行将始终被赋予rownum 1，返回的第二行将始终被赋予rownum 2，依此类推.

rownum值仅在分配后递增，因此任何查询，例如

select * from t where ROWNUM > 1

将从不返回任何结果.这个查询说:我不想看到返回给我的第一行，只有返回的那一行."这有点自相矛盾，所以什么也没有得到返回.

请参见问汤姆:关于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

解决方案

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.

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

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

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.

这篇关于Oracle/SQL:为什么查询"SELECT * FROM records where rownum> = 5 AND rownum< = 10" -返回零行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！