ROWNUM in Oracle SQL

最新推荐文章于 2024-04-19 16:11:29 发布

转载最新推荐文章于 2024-04-19 16:11:29 发布 · 624 阅读

文章标签：

#oracle #table #query #numbers #each #sql

Oracle 专栏收录该内容

15 篇文章

订阅专栏

本文详细介绍了Oracle数据库中ROWNUM伪列的功能与使用方法。ROWNUM用于为查询结果集中的每一行分配一个从1开始递增的行号。通过ROWNUM可以方便地实现对结果集的分页查询。需要注意的是，ROWNUM的值不能被修改，并且总是从1开始计数。

rownum is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.

After issuing a select statement, one of the last steps that oracle does is to assign an increasing (starting with 1, increased by 1) number to each row returned. The value of this row number can always be queried with rownum in a select statement:

select
  rownum, column_1, column_2 
from 
  table_1, table_2
where
  field_3 = 'some value'

It is important to realize that the first row's rownum is always 1. This implies that the following query won't return a single row:

select 
  column_1, column_2 
from 
  table_1, table_2
where
  field_3 = 'some value'
and rownum > 5

This is so because the first row would have to meet the following two mutually excluding criterias:

rownum is 1
rownum is 6 (rownum > 5)

In order to do this query in the (probably) intended spirit, a sub-query must be executed:

select 
  column_1, column_2 
from (
  select 
    rownum r_, column_1, column_2
  from 
    table_1, table_2
  where
    field_3 = 'some value'
)
where r_ > 5