Rownum的问题 [英] problem with rownum

问题描述

我有一个查询

select * from tablename where rownum =1;

此查询给出了所需的结果，但是如果我运行

This query is giving desired result but if i run

select * from tablename where rownum=2;

然后此查询给出空结果. 知道为什么吗?

Then this query is giving null result. Any idea why?

推荐答案

由于分配ROWNUM的方式，谓词WHERE rownum=2永远不会为真.例如，如果表中有5行，该算法将类似于

The predicate WHERE rownum=2 can never be true because of the way ROWNUM is assigned. If there are 5 rows in the table, for example, the algorithm would go something like

• 从表中检索第一行.将其分配为1的行数.
• 对照谓词检查该行编号，丢弃
• 从表中检索第二行.为它分配一个行数1.
• 对照谓词检查该行编号，丢弃
• 从表中检索第三行.为它分配一个行数1.
• 对照谓词检查该行编号，丢弃
• 从表中检索第四行.将其分配为1的行数.
• 对照谓词检查该行编号，丢弃
• 从表中检索第五行.为它分配一个行数1.
• 对照谓词检查该行编号，丢弃

• Retrieve the first row from the table. Assign it a rownum of 1.
• Check this rownum against the predicate, discard
• Retrieve the second row from the table. Assign it a rownum of 1.
• Check this rownum against the predicate, discard
• Retrieve the third row from the table. Assign it a rownum of 1.
• Check this rownum against the predicate, discard
• Retrieve the fourth row from the table. Assign it a rownum of 1.
• Check this rownum against the predicate, discard
• Retrieve the fifth row from the table. Assign it a rownum of 1.
• Check this rownum against the predicate, discard

由于结果集中没有第一行"，因此也就不会有第二行".您可以在不等式比较中使用ROWNUM，即

Since there is no "first row" in the result set, there can be no "second row". You can use ROWNUM in inequality comparisons, i.e.

SELECT *
  FROM table_name
 WHERE rownum <= 2

将返回2行.

如果您尝试标识表中的第二行"，则可能需要类似

If you are trying to identify the "second row" in a table, you'd likely want something like

SELECT *
  FROM (SELECT t.*,
               rank() over (order by some_column) rnk
          FROM table_name)
 WHERE rnk = 2

这篇关于Rownum的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！