当前位置：theitroad>SQL 订购后如何限制 Oracle 查询返回的行数？

SQL 订购后如何限制 Oracle 查询返回的行数？

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/470542/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 00:50:57 　来源:igfitidea点击:

How do I limit the number of rows returned by an Oracle query after ordering?

sqloraclepaginationsql-limit

提问by Mathieu Longtin

Is there a way to make an Oraclequery behave like it contains a MySQL limitclause?

有没有办法让Oracle查询表现得像它包含一个MySQL limit子句？

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 rownumpseudo-column, but it is evaluated beforeorder 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.

将返回一组按名称排序的随机十行，这通常不是我想要的。它也不允许指定偏移量。

采纳答案by sampathsris

Starting from Oracle 12c R1 (12.1), there isa row limiting clause. It does not use familiar LIMITsyntax, but it can do the job better with more options. You can find the full syntax here. (Also read more on how this works internally in Oracle in this answer).

从Oracle 12C R1（12.1）开始，那里是一个行限制性条款。它不使用熟悉的LIMIT语法，但可以通过更多选项更好地完成工作。您可以在此处找到完整的语法。（另请阅读本答案中有关 Oracle 内部如何工作的更多信息）。

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.

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

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 Nrows

获取第一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.

Get first Nrows, if Nthrow has ties, get all the tied rows

获取第一N行，如果N第 th行有平局，则获取所有平局行

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

前x% 行

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.

回答by Kosi2801

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 resultsat Oracle/AskTom for more information.

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

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 文章）

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 answerfor more examples. Thanks to Krumia for the hint.

有关更多示例，请参阅此答案。感谢 Krumia 的提示。

回答by zeldi

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)
  • But the winner is AskTom (with analytic following close behind)
  • 解释计划对所有三个选择显示相同的值 (323168)
  • 但赢家是 AskTom（分析紧随其后）

Selecting first 10 rows took:

选择前 10 行需要：

  • AskTom: 28-30 seconds
  • Analytical: 33-37 seconds
  • Short alternative: 110-140 seconds
  • AskTom：28-30 秒
  • 分析：33-37 秒
  • 短替代：110-140 秒

Selecting rows between 100,000 and 100,010:

选择 100,000 到 100,010 之间的行：

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

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

选择 9,000,000 到 9,000,010 之间的行：

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

回答by Leigh Riffel

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()但如果 name 有重复值，则可能返回比您预期更多的记录。

回答by beldaz

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;

回答by Bartek

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 predicatesof 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 Vertabeloblog:

在Vertabelo博客上的文章中了解有关分页查询的更多信息：

回答by Vlad Mihalcea

SQL Standard

SQL标准

As I explained in this article, the SQL:2008 Standard provides the following syntax to limit the SQL result set:

正如我在本文中所解释的，SQL:2008 标准提供了以下语法来限制 SQL 结果集：

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

Oracle 11g and older versions

Oracle 11g 及更早版本

Prior to version 12c, to fetch the Top-N records, you had to use a derived table and the ROWNUM pseudocolumn:

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

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

回答by Lukasz Szozda

As an extension of accepted answerOracle internally uses ROW_NUMBER/RANKfunctions. OFFSET FETCHsyntax is a syntax sugar.

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

It could be observed by using DBMS_UTILITY.EXPAND_SQL_TEXTprocedure:

可以通过使用DBMS_UTILITY.EXPAND_SQL_TEXT程序来观察：

Preparing sample:

准备样品：

CREATE TABLE rownum_order_test (
  val  NUMBER
);

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

Query:

询问：

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

is regular:

是常规的：

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 demo

db<>小提琴演示

Fetching expanded SQL text:

获取扩展的 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 TIESis expanded as RANK:

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

and offset:

和偏移：

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"

回答by Felipe Q. Giovanoni

Less SELECT statements. Also, less performance consuming. Credits to: [email protected]

更少的 SELECT 语句。此外，更少的性能消耗。致谢：[email protected]

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

回答by sandi

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 子句中，如下所示

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 作为过滤条件。

相关推荐

在单个 SQL 查询中插入多行？

在单个 SQL 查询中插入多行？

SQL 带有 If 语句 AND OR 条件的 Oracle 触发器

SQL 带有 If 语句 AND OR 条件的 Oracle 触发器

SQL 在多行上执行表值函数？

SQL 在多行上执行表值函数？

如何在 SQL Server 2005 中使用 LIMIT 关键字？

如何在 SQL Server 2005 中使用 LIMIT 关键字？

相关推荐
最近更新
标签