您可以为此使用子查询

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

还可以查看Oracle/AskTom上的关于ROWNUM和限制结果的主题以获得更多信息。

更新: 为了同时限制结果的下界和上界，事情变得有点臃肿

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(复制自指定的AskTom-article)

更新2: 从Oracle 12c(12.1)开始，就有了限制行或从偏移量开始的语法。

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

更多的例子请看这个答案。谢谢克鲁米亚的提示。

(未经测试)像这样的东西可能会起作用

WITH
base AS
(
    select *                   -- get the table
    from sometable
    order by name              -- in the desired order
),
twenty AS
(
    select *                   -- get the first 30 rows
    from base
    where rownum < 30
    order by name              -- in the desired order
)
select *                       -- then get rows 21 .. 30
from twenty
where rownum > 20
order by name                  -- in the desired order

还有一个分析函数秩，你可以用它来排序。

只有一个嵌套查询的解析解:

SELECT * FROM
(
   SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
) 
WHERE MyRow BETWEEN 10 AND 20;

Rank()可以代替Row_Number()，但如果name有重复的值，则可能返回比预期更多的记录。

少一些SELECT语句。同时，减少性能消耗。出处:anibal@upf.br

SELECT *
    FROM   (SELECT t.*,
                   rownum AS rn
            FROM   shhospede t) a
    WHERE  a.rn >= in_first
    AND    a.rn <= in_first;

我为以下方法做了一些性能测试:

Asktom

select * from (
  select a.*, ROWNUM rnum from (
    <select statement with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

分析

select * from (
  <select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW

短的替代

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

结果

表有1000万条记录，排序在一个未索引的datetime行上:

解释计划对所有三个选择显示相同的值(323168) 但是赢家是AskTom (analytic紧随其后)

选择前10行需要:

AskTom: 28-30秒 分析型:33-37秒 短选择:110-140秒

在100,000和100,010之间选择行:

AskTom: 60秒 分析性:100秒

在9,000,000和9,000,010之间选择行:

AskTom: 130秒 分析型:150秒

在Oracle 12c上(参见SQL参考中的行限制子句):

SELECT * 
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

在Oracle中，带有排序的分页查询非常棘手。

Oracle提供了一个ROWNUM伪列，它返回一个数字，指示数据库从表或连接视图集中选择行的顺序。

ROWNUM是一个伪列，它会给很多人带来麻烦。ROWNUM值不会永久地分配给行(这是一个常见的误解)。当实际分配ROWNUM值时，可能会令人困惑。ROWNUM值在行传递查询的过滤器谓词之后，但在查询聚合或排序之前分配给该行。

而且，ROWNUM值只有在赋值之后才会递增。

这就是为什么下面的查询不返回行:

 select * 
 from (select *
       from some_table
       order by some_column)
 where ROWNUM <= 4 and ROWNUM > 1; 

查询结果的第一行没有传递ROWNUM > 1谓词，因此ROWNUM不增加到2。因此，没有ROWNUM值大于1，因此，查询不返回任何行。

正确定义的查询应该是这样的:

select *
from (select *, ROWNUM rnum
      from (select *
            from skijump_results
            order by points)
      where ROWNUM <= 4)
where rnum > 1; 

在Vertabelo博客上的文章中可以找到更多关于分页查询的信息:

Oracle ROWNUM解释 Top-N和分页查询

从Oracle 12c R1(12.1)开始，有一个行限制子句。它没有使用我们熟悉的LIMIT语法，但是如果有更多的选项，它可以更好地完成这项工作。你可以在这里找到完整的语法。(也可以阅读更多关于如何在Oracle内部工作的答案)。

为了回答最初的问题，下面是问题:

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

(对于早期的Oracle版本，请参考此问题中的其他答案)

例子:

以下例子引用自链接页面，希望防止链接腐烂。

设置

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

表格里有什么?

SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

获取前N行

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

得到前N行，如果第N行有并列，得到所有并列的行

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

上x%的行

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

使用偏移量，对分页非常有用

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

您可以将偏移量与百分比结合使用

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

我已经开始准备Oracle 1z0-047考试，与12c进行了验证 在准备它的时候，我遇到了一个被称为“FETCH FIRST”的12c增强。 它允许您根据您的方便获取行/限制行。 它有几个可用的选项

- FETCH FIRST n ROWS ONLY
 - OFFSET n ROWS FETCH NEXT N1 ROWS ONLY // leave the n rows and display next N1 rows
 - n % rows via FETCH FIRST N PERCENT ROWS ONLY

例子:

Select * from XYZ a
order by a.pqr
FETCH FIRST 10 ROWS ONLY

select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
 FROM EMP ) EMP  where ROWID=5

大于数值

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID>5

更少的价值发现

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID=5

对于查询返回的每一行，ROWNUM伪列返回一个数字，指示Oracle从表或连接行的集合中选择该行的顺序。所选的第一行的ROWNUM为1，第二行为2，依此类推。

  SELECT * FROM sometable1 so
    WHERE so.id IN (
    SELECT so2.id from sometable2 so2
    WHERE ROWNUM <=5
    )
    AND ORDER BY so.somefield AND ROWNUM <= 100 

我已经在oracle server 11.2.0.1.0中实现了这个功能

SQL标准

从版本12c开始，Oracle支持SQL:2008标准，该标准提供了以下语法来限制SQL结果集:

SELECT
    title
FROM
    post
ORDER BY
    id DESC
FETCH FIRST 50 ROWS ONLY

Oracle 11g及更老版本

在版本12c之前，要获取Top-N记录，必须使用派生表和ROWNUM伪列:

SELECT *
FROM (
    SELECT
        title
    FROM
        post
    ORDER BY
        id DESC
)
WHERE ROWNUM <= 50

作为公认答案的扩展，Oracle内部使用ROW_NUMBER/RANK函数。OFFSET FETCH语法是语法糖。

可以使用DBMS_UTILITY来观察。EXPAND_SQL_TEXT过程:

准备样例:

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;
COMMIT;

查询:

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

常规:

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
               ROW_NUMBER() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rownumber" 
      FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$$_rownumber"<=5 ORDER BY "A1"."rowlimit_$_0" DESC;

db < > fiddle演示

获取展开的SQL文本:

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS ONLY',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

WITH TIES扩展为RANK:

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS WITH TIES',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
              RANK() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rank" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$$_rank"<=5 ORDER BY "A1"."rowlimit_$_0" DESC

和偏移量:

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/


SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
             ROW_NUMBER() OVER ( ORDER BY "A2"."VAL") "rowlimit_$$_rownumber" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
       WHERE "A1"."rowlimit_$$_rownumber"<=CASE  WHEN (4>=0) THEN FLOOR(TO_NUMBER(4)) 
             ELSE 0 END +4 AND "A1"."rowlimit_$$_rownumber">4 
ORDER BY "A1"."rowlimit_$_0"

使用21c版本，您可以简单地应用限制如下:

select * from course where ROWNUM <=10;