从薪水中选择rownum，其中rownum = 3;

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

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

4 个解决方案

#1

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.

当查询生成行时,Oracle会按顺序将值分配给ROWNUM - 因此,获取的第一行获取ROWNUM = 1,获取的第二行获取ROWNUM = 2,获取的第三行获取ROWNUM = 3等。注意 - 对于行要分配ROWNUM = 3必须获取前两行。这就是您的查询不返回任何行的原因。您要求数据库提取第三行 - 但从未提取过第1行和第2行。

To demonstrate, try running the following queries:

要演示,请尝试运行以下查询:

SELECT S.* FROM SALARY S;          -- Should return all rowsSELECT ROWNUM, S.* FROM SALARY S;  -- Should return all rows with ROWNUM prependedSELECT 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;

Share and enjoy.

分享和享受。

#2

You would need to do something like this

你需要做这样的事情

select rnum,sal  from  ( select sal, rownum rnum    from salary    order by sal desc )  where rnum = 3;

rownum is not assigned until after the predicate phase so rownum = 3 will always be false. Use a CTE or derived table then you can access the rownum from outside it.

直到谓词阶段之后才会分配rownum,因此rownum = 3将始终为false。使用CTE或派生表,然后您可以从外部访问rownum。

#3

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

This is incorrect. This will always return '3', because you are selecting ROW_NUMBER.

这是不正确的。这将始终返回'3',因为您正在选择ROW_NUMBER。

It should instead be "select *", as mentioned below:

它应该是“select *”,如下所述:

select * from (select rownum as row_number, s.* from salary s) where row_number = 3;

#4

Is rownum an actual column in your salary table? If not, depending on your DB type, rownum is likely not supported.

rownum是薪资表中的实际列吗?如果不是,则可能不支持rownum,具体取决于您的数据库类型。

#1