如何在排序后限制Oracle查询返回的行数?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause?

是否有一种方法可以使Oracle查询的行为像它包含一个MySQL限制子句一样?

In MySQL, I can do this:

在MySQL中，我可以这样做:

select * 
from sometable
order by name
limit 20,10

to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically.

要得到第21行到第30行(跳过前20行，给出下一个10)。这些行是在order by之后选择的，所以它实际上是按字母顺序从第20个名字开始的。

In Oracle, the only thing people mention is the rownum pseudo-column, but it is evaluated before order by, which means this:

在Oracle中，人们只提到rownum伪列，但它是在order by之前进行评估的，这意味着:

select * 
from sometable
where rownum <= 10
order by name

will return a random set of ten rows ordered by name, which is not usually what I want. It also doesn't allow for specifying an offset.

将返回一个由名称排序的10行随机集合，这通常不是我想要的。它也不允许指定偏移量。

13 个解决方案

#1

Starting from Oracle 12c R1 (12.1), there is a row limiting clause. It does not use familiar LIMIT syntax, but it can do the job better with more options. You can find the full syntax here.

从Oracle 12c R1(12.1)开始，有一个行限制子句。它不使用熟悉的限制语法，但是它可以用更多的选项更好地完成工作。您可以在这里找到完整的语法。

To answer the original question, here's the query:

要回答最初的问题，这里有一个问题:

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

(For earlier Oracle versions, please refer to other answers in this question)

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

---

Examples:

Following examples were quoted from linked page, in the hope of preventing link rot.

从链接页面中引用了以下示例，希望防止链接rot。

Setup

CREATE TABLE rownum_order_test (
  val  NUMBER
);

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

COMMIT;

What's in the table?

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.

Get first N rows

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

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

5 rows selected.

Get first N rows, if N^th row has ties, get all the tied rows

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.

Top x% of rows

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

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

4 rows selected.

Using an offset, very useful for pagination

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.

You can combine offset with percentages

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.

#2

You can use a subquery for this like

您可以使用子查询。

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

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

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

Update: To limit the result with both lower and upper bounds things get a bit more bloated with

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

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;

(Copied from specified AskTom-article)

(从指定AskTom-article复制)

Update 2: Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

更新2:从Oracle 12c(12.1)开始，可以使用一种语法来限制行或从偏移量开始。

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

See this answer for more examples. Thanks to Krumia for the hint.

更多的例子可以看到这个答案。感谢克鲁米娅的暗示。

#3

I did some performance testing for the following approaches:

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

Asktom

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

Analytical

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

Short Alternative

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

Results

Table had 10 million records, sort was on an unindexed datetime row:

表有1000万条记录，排序在一个未被索引的日期时间行上:

• Explain plan showed same value for all three selects (323168)
• 解释计划对所有三个选择显示相同的值(323168)
• But the winner is AskTom (with analytic following close behind)
• 但获胜者是AskTom(紧跟其后的分析人士)

Selecting first 10 rows took:

选择前10行:

• AskTom: 28-30 seconds
• AskTom:28 - 30秒
• Analytical: 33-37 seconds
• 分析:33-37秒
• Short alternative: 110-140 seconds
• 短的选择:110 - 140秒

Selecting rows betwwen 100,000 and 100,010:

选择行betwwen 100,000和100,010:

• AskTom: 60 seconds
• AskTom:60秒
• Analytical: 100 seconds
• 分析:100秒

Selecting rows between 9,000,000 and 9,000,010:

选择9,000,000到9000,010之间的行:

• AskTom: 130 seconds
• AskTom:130秒
• Analytical: 150 seconds
• 分析:150秒

#4

An analytic solution with only one nested query:

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

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

Rank() could be substituted for Row_Number() but might return more records than you are expecting if there are duplicate values for name.

Rank()可以替代Row_Number()，但是如果名称有重复的值，则可能会返回更多的记录。

#5

On Oracle 12c (see row limiting clause in SQL reference):

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

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

#6

Pagination queries with ordering are really tricky in Oracle.

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

Oracle provides a ROWNUM pseudocolumn that returns a number indicating the order in which the database selects the row from a table or set of joined views.

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

ROWNUM is a pseudocolumn that gets many people into trouble. A ROWNUM value is not permanently assigned to a row (this is a common misunderstanding). It may be confusing when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes filter predicates of the query but before query aggregation or sorting.

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

What is more, a ROWNUM value is incremented only after it is assigned.

更重要的是，在分配了ROWNUM值之后，它才会递增。

This is why the followin query returns no rows:

这就是为什么后面的查询不会返回任何行:

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

The first row of the query result does not pass ROWNUM > 1 predicate, so ROWNUM does not increment to 2. For this reason, no ROWNUM value gets greater than 1, consequently, the query returns no rows.

查询结果的第一行不会传递ROWNUM > 1谓词，因此ROWNUM不会增加到2。由于这个原因，没有ROWNUM值大于1，因此查询返回无行。

Correctly defined query should look like this:

正确定义的查询应该如下所示:

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

Find out more about pagination queries in my articles on Vertabelo blog:

在Vertabelo博客上找到关于分页查询的更多信息:

• Oracle ROWNUM Explained
• 甲骨文ROWNUM解释
• Top-N and pagination queries
• 头n个和分页查询

#7

Less SELECT statements. Also, less performance consuming. Credits to: [e-mail]

更少的SELECT语句。同时,更少的性能消耗。学分:[e-mail]

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

#8

If you are not on Oracle 12C, you can use TOP N query like below.

如果您不在Oracle 12C上，可以使用下面的TOP N查询。

SELECT *
 FROM
   ( SELECT rownum rnum
          , a.*
       FROM sometable a 
   ORDER BY name
   )
WHERE rnum BETWEEN 10 AND 20;

You can even move this from clause in with clause as follows

你甚至可以用下面的子句来移动这个from子句。

WITH b AS
( SELECT rownum rnum
      , a.* 
   FROM sometable a ORDER BY name
) 
SELECT * FROM b 
WHERE rnum BETWEEN 10 AND 20;

Here actually we are creating a inline view and renaming rownum as rnum. You can use rnum in main query as filter criteria.

实际上，我们正在创建一个内联视图，并将rownum改名为rnum。可以在主查询中使用rnum作为筛选条件。

#9

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

greater then values find out

更大的价值会发现。

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

less then values find out

更少的价值被发现。

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

#10

I'v started preparing for Oracle 1z0-047 exam, validated against 12c While prepping for it i came across a 12c enhancement known as 'FETCH FIRST' It enables you to fetch rows /limit rows as per your convenience. Several options are available with it

我开始准备Oracle 1z0-047的测试，在12c的时候进行了验证，而在准备它的时候，我遇到了一个12c的增强，称为“FETCH FIRST”，它使您可以根据您的方便获取行/限制行。有几个选项可供选择。

- 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

Example:

例子:

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

#11

In oracle

在oracle中

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

VAL

    10
    10
     9
     9
     8

5 rows selected.

5行选择。

SQL>

SQL >

#12

(untested) something like this may do the job

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

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

There is also the analytic function rank, that you can use to order by.

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

#13

Same as above with corrections. Works but definitely not pretty.

和上面的修正一样。工作，但肯定不漂亮。

   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

Honestly, better to use the above answers.

诚实地说，最好使用上面的答案。