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

最新推荐文章于 2023-12-04 10:39:38 发布
最新推荐文章于 2023-12-04 10:39:38 发布
阅读量327 收藏
点赞数
文章标签： sql oracle pagination limit
原文链接：https://stackoom.com/question/1yPO/订购后如何限制Oracle查询返回的行数
版权

有没有一种方法可以使Oracle查询像包含MySQL limit子句那样工作？

在MySQL ，我可以这样做： 

select * 
from sometable
order by name
limit 20,10

以获得第21至第30行（跳过前20行，给出下10行）。 这些行是按order by选择order by ，因此实际上按字母顺序从第20个名称开始。

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

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

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

#1楼

在Oracle 12c上（请参阅SQL参考中的行限制子句）： 

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

#2楼

在Oracle中，带有排序的分页查询确实很棘手。

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

ROWNUM是一个伪列，它使很多人陷入困境。 ROWNUM值未永久分配给行（这是常见的误解）。 实际分配ROWNUM值时可能会造成混淆。 ROWNUM值在通过查询的过滤谓词之后但在查询聚合或排序之前被分配给一行。

此外，仅在分配ROWNUM值后，它才会递增。

这就是为什么followin查询不返回任何行的原因： 

 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博客上的文章中找到有关分页查询的更多信息：

#3楼

在甲骨文 

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

VAL值 

    10
    10
     9
     9
     8

已选择5行。

SQL>

#4楼

从Oracle 12C R1（12.1）开始， 有一个行限制性条款 。 它没有使用熟悉的LIMIT语法，但是可以通过更多选项更好地完成这项工作。 您可以在此处找到完整的语法 。

要回答原始问题，以下是查询： 

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.

#5楼

我已经开始为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

例： 

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

#6楼

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

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

您甚至可以如下将with子句中的from子句移动 

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

实际上，我们在这里创建一个内联视图，并将rownum重命名为rnum。 您可以在主查询中使用rnum作为过滤条件。

#7楼

与上述相同，但有更正。 可行，但绝对不漂亮。 

   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

老实说，最好使用上述答案。

#8楼

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

#9楼

您可以像这样使用子查询 

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

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

更新 ：为了限制结果的上下限，事情变得更加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;

（从指定的AskTom文章复制）

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

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

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

#10楼

（未经测试）像这样的工作 

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

还有一个分析功能等级，可用于排序。

#11楼

仅包含一个嵌套查询的解析解决方案： 

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

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

#12楼

更少的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;

#13楼

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

#14楼

SQL标准

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

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

#15楼

作为公认答案的扩展，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"

#16楼

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

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

  • 解释计划对所有三个选择显示相同的值（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秒
asdfgh0077
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle查询所有表行数sql脚本
04-27
可查询Oracle当前用户下所有表的记录数,方便查询
oracle fetch报错,Oralce fetch first ORA-00933 SQL命令未正确结束
weixin_39992788的博客
04-08 1211
项目使用的是MyBatis，之前用的是DB2的数据库，现切换到Oracle时，其中一条SQL语句报错ORA-00933：select *from com_transactionwhere 1=1and user_id = ?order by id descfetch first 10 rows only错误：Caused by: org.springframework.jdbc.BadSqlGra...
Oracle Fetch子句
weixin_30787531的博客
07-24 927
Oracle Fetch子句 作者：初生不惑Oracle基础评论：0 条Oracle技术QQ群：175248146 在本教程中，将学习如何使用OracleFETCH子句来限制查询返回的行数。 Oracle FETCH子句简介 一些RDBMS(如MySQL和PostgreSQL)使用LIMIT子句来检索查询生成的一部分行记录。 请参阅示例数据库中的产品(products)和库存...
Oracle 三种分页方法
logqq的专栏
03-14 2万+
使用ROW_NUMBER() OVER()进行分页查询的优点是灵活性高，可以实现各种不同的分页方式，缺点是语法较为复杂。上述查询语句的意思是查询my_table表中的前30条记录，然后在这30条记录中取出第20到第30条记录作为结果返回。使用ROWNUM进行分页查询的优点是简单易懂，缺点是在查询大量数据时性能较差。Oracle的三层分页指的是在进行分页查询时，使用三种不同的方式来实现分页效果，分别是使用ROWNUM、使用OFFSET和FETCH、使用ROW_NUMBER() OVER()
Oracle 12c新增fetch分页子句之好处
xuduono1的博客
06-21 1439
Oracle 12c新增fetch分页子句之好处
Oracle排序后限制查询返回的行数的方法
滕青山博客
11-19 3197
上面的语句将返回按名称排序的。
Oracle基础知识及查询优化
木子叶的奔跑
09-11 950
文章目录Oracle基础知识总结一、基础知识（DML）1、Select语句2、Order By 、Group By子句3、Distinct子句4、Where子句5、And子句6、Or子句7、 Fetch子句OFFSET子句FETCH子句8、IN子句9、Between子句10、Like子句通配符2、Insert语句3、Update语句4、Delete语句二、DDL语句1、数据库创建2、数据库删除3、创建表4、修改表5、删除表6、截断表（彻底释放表占用空间）7、闪回8、约束9、视图三、索引1、建设原则2、创建索
Oracle实现limit
热门推荐
云在青天水在瓶
10-25 4万+
一、背景 oracle数据库不支持mysql、postgresql的limit功能，但是可以通过rownum（oracle系统顺序分配为从查询返回的行的编号，从1开始排）来限制返回结果集的行数，达到同样的效果。 二、用法 -- 查询前10行记录 select * from XXX where rownum <= 10 -- 查询从10行到20行的记录 select * from XXX where rownum < 21 minus select * from XXX where r
Oracle查询优化改写技巧与案例2.zip
06-22
《Oracle查询优化改写技巧与案例》不讲具体语法，只是以案例的形式介绍各种查询语句的用法。第1~4章是基础部分，讲述了常用的各种基础语句，以及常见的错误和正确语句的写法。这部分的内容应熟练掌握，因为日常查询...
Oracle Exchange and Oracle? Sourcing System Operator Implementat
04-29
Oracle Exchange and Oracle? Sourcing System Operator Implementation Guide Release 6.2
ORACLE EBS怎么样？
04-11
ORACLE EBS是是Oracle旗下的ERP平台，正在选型中的您，一定非常关心 ORACLE EBS用户口碑怎么样？优缺点有哪些？ 想了解这些信息，您应该到选型宝，B2B的大众点评，看看用户对ORACLE ERP产品的实名点评。
oracle12C offset子句 FETCH子句
objact的博客
01-01 1138
The row_limiting_clause allows you to limit the rows that are returned by the query. Queries that order data and then limit row output are widely used and are often referred to as Top-N queries. You can specify the number of rows or percentage of rows to .
Oracle的rownum查单表限制条数
zsj_javaer的博客
09-21 2989
1.查小于10行的数据。2.查大于10小于20。rownum查两数之间的条数的正确写法
Oracle 查询语句限制只选择最前面几行，和最后面几行的实现方式。
最新发布
全栈攻略
12-04 1095
查询组后几行时，仅仅使用ROWNUM是不够的，还需要使用子查询SELECT *FROM (SELECT *实际使用时将your_table替换为要查询的表名，替换为用于排序的列名。以上查询将按降序排序记录，并选择前10项。然后，外部查询将再次按升序排序结果，以确保最后10项记录按升序显示。
Oracle数据库实现limit功能
fen_fen的专栏
05-16 2265
Oracle数据库实现limit功能
Oracle 分页查询 rownum 和 offset
howeres的博客
10-04 5613
Rownum A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned . rownum 主要有两类用处 处理 top N ； 分页查询； rownum
Oracle中的分页查询
m0_71406734的博客
05-11 1万+
Oracle中的分页查询
oracle里的限制返回行数量
左直拳的马桶_日用桶
08-17 7305
这个问题，SQL SERVER是很方便的，使用top就行了。mysql也不赖，limit。但号称龙头老大的oracle呢？不存在的。要曲线救国。 使用rownum。这是一个伪列。使用的时候，如果有排序的话，不能直接就用，否则出来的效果很奇怪，要嵌套使用。 比如说，我想找考试成绩排名前5的记录，不能这样写： select * from exam where rownum<=5 order by...
oracle 12c 新特性之（相同字段上的多重索引、ddl 日志、限制PGA的大小、分页查询）
张冲andy的博客
05-07 871
1. 相同字段上的多重索引    在Oracle 12c R1之前，一个字段是无法以任何形式拥有多个索引的。或许有人会想知道为什么通常一个字段需要有多重索引，事实上需要多重索引的字段或字段集合是很多的。在12c R1中，只要索引类型的形式不同，一个字段就可以包含在一个B-tree索引中，同样也可以包含在Bitmap索引中。注意，只有一种类型的索引是在给定时间可见可用的。 SQL>creat
mybatis操作oracle执行批量操作时为什么返回-1，如何让它返回实际影响行数？
06-09
这是因为Oracle对于批量更新操作，返回的是SQL语句的执行次数，而不是实际影响的行数。 为了让MyBatis返回实际影响行数，可以在Mapper.xml文件中的对应语句的标签中设置useGeneratedKeys="true"和keyProperty属性，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值