用ChatGPT解决这个技术问题 Extra ChatGPT

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

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

在 MySQL 中，我可以这样做： 

select * 
from sometable
order by name
limit 20,10

获得第 21 到第 30 行（跳过前 20 行，给出接下来的 10 行）。行是在 order by 之后选择的，因此它实际上按字母顺序从第 20 个名称开始。

在 Oracle 中，人们唯一提到的是 rownum 伪列，但它是在 before order by 之前评估的，这意味着： 

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

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

在 SQL:2008 中标准化。
Tom Kyte 宣布了 Oracle 12c 的限制...
获取结果集中的下一页？
@YaroslavShabalin 特别是，分页搜索一直使用这种模式。几乎所有具有任何类型搜索功能的应用程序都会使用它。另一个用例是仅加载长列表或表格客户端的一部分，并为用户提供扩展选项。
@YaroslavShabalin 除非基础数据因 ORDER BY 而发生变化，否则您无法获得不同的结果集。这就是首先订购的全部意义所在。如果基础数据发生变化并且您的结果集因此而变化，那么为什么不向用户显示更新的结果而不是过时的信息呢？此外，状态管理是一个需要尽可能避免的瘟疫。这是复杂性和错误的持续来源；这就是为什么函数式变得如此受欢迎的原因。你什么时候知道使内存中的整个结果集过期？在 Web 中，您无法知道用户何时离开。

T
Tomalak

您可以为此使用子查询 

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

另请查看 Oracle/AskTom 上的主题 On ROWNUM and limiting results 以了解更多信息。

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

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

更新 2：从 Oracle 12c (12.1) 开始，有一种语法可用于限制行或从偏移量开始。 

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

有关更多示例，请参阅 this answer。感谢 Krumia 的提示。

这绝对是这样做的方法，但请注意（正如ask tom 文章所说）查询性能会随着您的最大rownum 增加而降低。对于只想查看前几页的查询结果，这是一个很好的解决方案，但是如果您将其用作代码在整个表中分页的机制，则最好重构代码
+1 您的低/高版本实际上帮助我解决了一个问题，即仅仅一个上限 rownum 子句就大大减慢了我的查询速度。
Leigh Riffel“只有一个嵌套查询的分析解决方案”就是其中之一。
AskTom 文章也有一个优化器提示，它使用 SELECT /*+ FIRST_ROWS(n) / a., rownum rnum 结束斜杠前面应该有一个星号。 SO正在清理它。
请注意，对于 Oracle 11，带有 ROWNUM 的外部 SELECT 将阻止您在 UpdatableResultSet（带有 ORA-01446）上调用 deleteRow - 期待 12c R1 的更改！
C
Community

从 Oracle 12c R1 (12.1) 开始，有一个 row limiting clause。它不使用熟悉的 LIMIT 语法，但可以通过更多选项更好地完成工作。您可以找到 full syntax here。 （另请阅读 this answer 中有关这在 Oracle 内部如何工作的更多信息）。

要回答原始问题，请输入以下查询： 

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

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

例子：

以下示例来自 linked page，希望防止链接失效。

设置 

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.
We need tool support for keyset pagination
只是为了扩展： OFFSET FETCH 语法是语法糖。 Details
我们如何获得 Oracle 11G 中的 LIMIT 和 OFFSET ？
@Pra_A 11G 中没有对 LIMIT/OFFSET 的原生支持。如果您检查其他答案，他们都以一种或其他方式实际实施了限制和偏移。
g
giannis christofakis

我对以下方法进行了一些性能测试：

阿斯通 

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 万条记录，排序在未索引的日期时间行上：

解释计划显示所有三个选择的值相同 (323168)

但获胜者是 AskTom（分析紧随其后）

选择前 10 行需要：

AskTom：28-30 秒

分析：33-37 秒

短替代：110-140 秒

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

AskTom：60 秒

分析：100 秒

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

AskTom：130 秒

分析：150 秒

不错的作品。您是否尝试过使用 between 而不是 >= 和 <= 的简短替代方法？
@MathieuLongtin BETWEEN 只是 >= AND <= (stackoverflow.com/questions/4809083/between-clause-versus-and) 的简写
zeldi - 这是哪个版本的？ Oracle 在 11.1 中改进了分析性能。和 11.2。
@Leigh Riffel 这是 10.2.0.5；有一天我可能会花时间检查 11i 版本。
我进行了一些快速测试，并得到了 12c 的类似结果。新的 offset 语法具有与分析方法相同的计划和性能。
s
sampathsris

只有一个嵌套查询的分析解决方案： 

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

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

我喜欢分析。您可能想澄清 Rank() 和 Row_Number() 之间的行为差异。
确实，不知道为什么我不考虑重复。因此，在这种情况下，如果名称有重复值，那么 RANK 可能会提供比您预期更多的记录，因此您应该使用 Row_Number。
如果提到 rank()，还值得注意的是 dense_rank()，它可能对输出控制更有用，因为后者不会“跳过”数字，而 rank() 可以。无论如何，row_number() 最适合这个问题。另一个不是这种技术适用于任何支持上述功能的数据库。
V
Vlad Mihalcea

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 中从来没有一种语法支持使用“从 {TableName} 中选择前 N *”或类似的东西？
@Ak777 不。那只是 SQL Server。
s
sampathsris

在 Oracle 12c 上（参见 SQL reference 中的行限制子句）： 

SELECT * 
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
当然，到目前为止，他们必须使用与其他所有人完全不同的语法
显然，在与所有其他供应商坐下来就 SQL:2008 中的 LIMIT 达成一致之后，他们不得不从微软的书中吸取教训并打破标准。
有趣的是，我最近听说最新的标准包括这种语法，所以可能是 Oracle 在实施之前先推出了它。可以说它比 LIMIT ... OFFSET 更灵活
@Derek：是的，不遵守标准是令人遗憾的。但是 12cR1 中新引入的功能比 LIMIT n, m 更强大（请参阅我的答案）。再说一次，Oracle 应该将 LIMIT n, m 实现为语法糖，因为它等同于 OFFSET n ROWS FETCH NEXT m ROWS ONLY。
@Derek：实际上，我只是在 PostgreSQL 手册 postgresql.org/docs/9.0/static/sql-select.html#AEN69535 中注意到这句话“子句 LIMIT 和 OFFSET 是 PostgreSQL 特定的语法，也被 MySQL 使用。SQL:2008 标准引入了子句 OFFSET ... FETCH {FIRST |NEXT} ...用于相同的功能”。所以 LIMIT 从来都不是标准的一部分。
B
Bartek

在 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 和分页查询

查询结果的第一行没有通过 ROWNUM > 1 谓词 (...) – 赞成解释这一点。
极好的！直到今天，oracle 还没有针对此问题的性能解决方案。例如，在两者之间，它非常慢！
L
Lukasz Szozda

作为 accepted answer 的扩展，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 demo

获取扩展的 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"
F
Felipe Q. Giovanoni

更少的 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;
此外，这是完全错误的答案。问题是关于排序后的限制。所以rownum应该不在子查询中。
H
Hafiz Muhammad Shafiq

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

select * from course where ROWNUM <=10;
阅读我的问题的第二部分。这不起作用，它的存在时间比 21c 长得多
a
arjun gaur

我已经开始准备 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
stackoverflow.com/a/26051830/635608 - 这已在其他答案中提供。请不要发布几个月前已经发布的内容。
哦，当然，没有通过每一个答案，我很早就遇到了子查询，会记住这一点。
S
Sumesh TG

对于查询返回的每一行，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 服务器 11.2.0.1.0 中实现了这个

当问题询问有关限制有序行的问题时投反对票，而您甚至没有订单
@PiotrDobrogost 明白这不是一项艰巨的任务，排序关键字对于所有 rdbms 都很常见，只有限制有变化。
M
Mehul Akabari 
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
Leigh Riffel 已经发布了作为基于 ROW_NUMBER() 的解决方案的 Downvote。令人上瘾的是，显示的代码中存在语法错误。
E
EvilTeach

（未经测试）这样的事情可能会完成这项工作 

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

还有分析函数排名，您可以使用它来排序。

这不会返回单行，因为 ROWNUM 是结果集中的一列，因此最后一个 WHERE 条件将始终为假。另外，您不能使用 ROWNUM 和 ORDER BY 保证 ORDER。
出色的。让我们把它留在这里作为对其他人的警告。

关注公众号,不定期副业成功案例分享
关注公众号

不定期副业成功案例分享

想领先一步获取最新的外包任务吗？

立即订阅

HuntsBot(狩猎机器人)，一站式外包任务、远程工作、产品创意分享与订阅平台，支持钉钉、飞书、企业微信、邮箱、Telegram机器人订阅。平台会以及时、稳定、可靠的技术把外包任务需求、远程工作机会、产品创意等推送给每一位订阅的用户。

简体中文 English

平台

支持

联系我们

在使用过程中有任何问题或建议，可以通过以下方式联系我们：

微信公众号: 火星来客

Email: huntsbot@xinbeitime.com

Copyright© 2022-2023 www.huntsbot.com 浙ICP备2022000860号-4 All Rights Reserved.