从薪水中选择rownum，其中rownum = 3; [英] select rownum from salary where rownum=3;

问题描述

如何使用"rownum"关键字从任何表中检索第三行(我使用的是oracle-10g)

How to retrieve third row from any table using "rownum" key word ( i am using oracle-10g)

推荐答案

随着查询产生行，Oracle依次将值分配给ROWNUM-因此，获取的第一行获取ROWNUM = 1，获取的第二行获取ROWNUM = 2 ，则提取的第三行将获得ROWNUM = 3，以此类推.注意-对于要分配给ROWNUM = 3的行，必须先获取前两行.这就是为什么您的查询不返回任何行的原因.您要向数据库查询已提取的第三行-但从未提取过行1和2.

Oracle assigns values to ROWNUM sequentially as rows are produced by the query - thus, the first row fetched gets ROWNUM=1, the second row fetched gets ROWNUM=2, the third row fetched gets ROWNUM=3, etc. Notice - for a row to be assigned ROWNUM=3 two preceding rows MUST be fetched. And this is why your query returns no rows. You're asking the database for the third row fetched - but rows 1 and 2 have never been fetched.

为演示，请尝试运行以下查询:

To demonstrate, try running the following queries:

SELECT S.* FROM SALARY S;          -- Should return all rows
SELECT ROWNUM, S.* FROM SALARY S;  -- Should return all rows with ROWNUM prepended
SELECT ROWNUM, S.* FROM SALARY WHERE ROWNUM=3;  -- Should return no rows

要解决您的问题，请尝试以下操作:

To work around your problem, try the following:

SELECT ROW_NUMBER FROM
  (SELECT ROWNUM AS ROW_NUMBER, S.* FROM SALARY S)
  WHERE ROW_NUMBER = 3;

分享并享受.

这篇关于从薪水中选择rownum，其中rownum = 3;的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！