有没有办法使一个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行按名称排序的集合，这通常不是我想要的。它也不允许指定偏移量。
当前回答
从Oracle 12c R1(12.1)开始，有一个行限制子句。它没有使用我们熟悉的LIMIT语法，但是如果有更多的选项，它可以更好地完成这项工作。你可以在这里找到完整的语法。(也可以阅读更多关于如何在Oracle内部工作的答案)。
为了回答最初的问题，下面是问题:
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.
其他回答
从Oracle 12c R1(12.1)开始，有一个行限制子句。它没有使用我们熟悉的LIMIT语法，但是如果有更多的选项，它可以更好地完成这项工作。你可以在这里找到完整的语法。(也可以阅读更多关于如何在Oracle内部工作的答案)。
为了回答最初的问题，下面是问题:
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.
对于查询返回的每一行，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语句。同时，减少性能消耗。出处:anibal@upf.br
SELECT *
FROM (SELECT t.*,
rownum AS rn
FROM shhospede t) a
WHERE a.rn >= in_first
AND a.rn <= in_first;
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
