在oracle中使用rownum获取数据 [英] fetching data using rownum in oracle

问题描述

我在oracle中有一个查询，要使用rownum从表中获取数据，但没有得到任何数据.
我的查询是这样的:

select * from table-name where rownum<5

这是错误的查询，用于获取行号小于5的数据.
当我使用诸如:
的查询时 select * from table-name where rownum<=4
比它会给出一个结果记录.

我的问题是这里出什么问题了?
这是语法错误还是其他???.

解决方案

rownum是伪列，该伪列在应用where子句后对结果集中的行进行计数.

  SELECT table_name
    FROM user_tables
    WHERE rownum > 2;
TABLE_NAME                     
------------------------------

0 rows selected

但是，无论表中的行数如何，此查询将始终返回零行.

要解释这种行为，我们需要了解Oracle如何处理ROWNUM.在将ROWNUM分配给一行时，Oracle从1开始，并且仅在选择一行时才递增该值；否则，Oracle将从1开始递增该值.也就是说，当WHERE子句中的所有条件都满足时.由于我们的条件要求ROWNUM大于2，所以不会选择任何行，并且ROWNUM永远不会增加到1以上.

http://blog.lishman.com/2008/03/rownum.html

另一个stackoverflow链接

已编辑

我在 oracle 网站更好

对于ROWNUM值大于正整数的条件测试始终为false.例如，此查询不返回任何行:

SELECT * FROM employees
    WHERE ROWNUM > 1;

提取的第一行的ROWNUM分配为1，并使条件为false.现在要获取的第二行是第一行，并且还分配了ROWNUM为1并使条件为假.随后，所有行均不满足该条件，因此不返回任何行.

您还可以使用ROWNUM为表的每一行分配唯一值，如本例所示:

I have a query in oracle to fetch data from table using rownum but i didn't get any data.
My query is like this :

select * from table-name where rownum<5

is this is a wrong query to fetch data whose row number is less than 5.
when i used query like :
select * from table-name where rownum<=4
than it will gives a result record.

My question is what is wrong here with ?
Is this is syntax error or anything else??..

解决方案

rownum is a pseudo column that counts rows in the result set after the where clause has been applied.

  SELECT table_name
    FROM user_tables
    WHERE rownum > 2;
TABLE_NAME                     
------------------------------

0 rows selected

However, this query will always return zero rows, regardless of the number of rows in the table.

To explain this behaviour, we need to understand how Oracle processes ROWNUM. When assigning ROWNUM to a row, Oracle starts at 1 and only increments the value when a row is selected; that is, when all conditions in the WHERE clause are met. Since our condition requires that ROWNUM is greater than 2, no rows are selected and ROWNUM is never incremented beyond 1.

http://blog.lishman.com/2008/03/rownum.html

another stackoverflow link

Edited

this paragraph i find on oracle website which is much better

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM employees
    WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

这篇关于在oracle中使用rownum获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！