oracle - 如何限制Oracle查询返回的行数?

  显示原文与译文双语对照的内容

是否有一种方法使得Oracle查询行为像包含一个 MySQL limit 子句?

在MySQL中,我可以这样做:


select * 
from sometable
order by name
limit 20,10

获取 21到 30行的行。 行是在 order by 之后选定的,所以它实际上以字母顺序从 20开始。

在Oracle中,唯一的一件事是人们一提到 rownum pseudo-column 之前,但它是评估 order by,这意味着这个:


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

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

时间:

你可以使用子查询进行如下操作


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

于更多information,相关也看一下主题在 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 12 ( 12.1 ) 开始,有一个语法可以限制行或者从偏移量开始。


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

有关更多示例,请参见 感谢Krumia的提示。

我执行了一些性能测试:

Asktom 。)


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

分析方法


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

c 。) 简短的备选方案


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

结果:

表有 10条记录,排序位于非索引日期时间行中:

  • 解释计划为所有三个选择显示相同的值( 323168 )
  • 但是赢家是 AskTom ( 分析后紧跟了分析)

选择前 10行:

  • AskTom: 28 -30秒
  • 分析:33 -37秒
  • 短替代:110 -140秒

选择行 betwwen 100.000和 100.010:

  • AskTom: 60秒
  • 分析:100秒

在 9.000.000和 9.000.010之间选择行:

  • AskTom: 130秒
  • 分析:150秒

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


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

Rank() 可以替代 Row_Number(),但如果有重复的名称,则可能会返回比预期更多的记录。

从甲骨文 12 c R1 ( 12.1 ) 出发,是有一个限制子句 行。 它不使用熟悉的LIMIT,但它可以更好地使用更多选项。 你可以在这里找到完整语法

要回答原始问题,下面是查询:


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


例如:

下面的例子引用了链接页面,希望防止链接错误。

安装程序


CREATE TABLE rownum_order_test (
 val NUMBER
);

INSERT ALL
 INTO rownum_order_test
 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 行,如果 Nth 行有关系,获取所有绑定的行


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 12 ( 参见 SQL参考中的行限制子句) 上:


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

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

使用排序的分页查询在Oracle中非常棘手。

在桌子旁或者一组加入views,甲骨文提供了一个ROWNUM伪列,它返回一个数字,指示数据库将选择row.的顺序

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 博客中查找关于分页查询的更多信息:

上面的更正相同。 工作但绝对不是很好。


 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

老实说,最好使用上面的答案。

( 未经测试) 这样的事情可能会做这个工作


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

还有解析函数秩,你可以用它来 ORDER BY 。

在oracle中


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

VAL


 10
 10
 9
 9
 8

选择了5 行。

>

...