有没有办法使一个Oracle查询行为像它包含一个MySQL限制子句?
在MySQL中，我可以这样做:
select *
from sometable
order by name
limit 20,10
要得到第21行到第30行(跳过前20行，给出接下来的10行)。这些行是按顺序选择的，所以实际上是从第20个名字的字母顺序开始的。
在Oracle中，人们唯一提到的是rownum伪列，但它在order by之前求值，这意味着:
select *
from sometable
where rownum <= 10
order by name
将返回一个随机的10行按名称排序的集合，这通常不是我想要的。它也不允许指定偏移量。
当前回答
少一些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;
其他回答
(未经测试)像这样的东西可能会起作用
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伪列返回一个数字，指示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 server 11.2.0.1.0中实现了这个功能
您可以为此使用子查询
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
还可以查看Oracle/AskTom上的关于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 12c(12.1)开始，就有了限制行或从偏移量开始的语法。
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
更多的例子请看这个答案。谢谢克鲁米亚的提示。
只有一个嵌套查询的解析解:
SELECT * FROM
(
SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
)
WHERE MyRow BETWEEN 10 AND 20;
Rank()可以代替Row_Number()，但如果name有重复的值，则可能返回比预期更多的记录。
少一些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;
