SQL ROWNUM 如何返回特定范围之间的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4552769/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL ROWNUM how to return rows between a specific range
提问by code511788465541441
How can I return a specific range of ROWNUMvalues?
如何返回特定范围的ROWNUM值？
I'm trying the following:
我正在尝试以下操作：
select * from maps006 where rownum >49 and rownum <101
This returns only rows matching the <operator.
这仅返回与<运算符匹配的行。
回答by Michael Pakhantsov
SELECT * from
(
select m.*, rownum r
from maps006 m
)
where r > 49 and r < 101
回答by Quassnoi
SELECT *
FROM (
SELECT q.*, rownum rn
FROM (
SELECT *
FROM maps006
ORDER BY
id
) q
)
WHERE rn BETWEEN 50 AND 100
Note the double nested view. ROWNUMis evaluated before ORDER BY, so it is required for correct numbering.
注意双重嵌套视图。ROWNUM在 之前求值ORDER BY，因此需要正确编号。
If you omit ORDER BYclause, you won't get consistent order.
如果省略ORDER BY子句，则不会获得一致的顺序。
回答by Lalit Kumar B
I know this is an old question, however, it is useful to mention the new featuresin the latest version.
我知道这是一个老问题，但是，更何况它是有用的新功能的最新版本。
From Oracle 12conwards, you could use the new Top-n Row limiting feature. No need to write a subquery, no dependency on ROWNUM.
从Oracle 12c开始，您可以使用新的Top-n Row 限制功能。无需编写子查询，无需依赖 ROWNUM。
For example, the below query would return the employees between 4th highest till 7th highest salaries in ascending order:
例如，下面的查询将按升序返回第 4 高到第 7 高薪水之间的员工：
SQL> SELECT empno, sal
2 FROM emp
3 ORDER BY sal
4 OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;
EMPNO SAL
---------- ----------
7654 1250
7934 1300
7844 1500
7499 1600
SQL>
回答by cpomp
I was looking for a solution for this and found this great article explaining the solutionRelevant excerpt
我正在为此寻找解决方案，并找到了这篇很好的 文章，解释了解决方案相关摘录
My all-time-favorite use of ROWNUM is pagination. In this case, I use ROWNUM to get rows N through M of a result set. The general form is as follows:
我一直以来最喜欢使用 ROWNUM 的是分页。在这种情况下，我使用 ROWNUM 来获取结果集的 N 到 M 行。一般形式如下：
select * enter code here
from ( select /*+ FIRST_ROWS(n) */
a.*, ROWNUM rnum
from ( your_query_goes_here,
with order by ) a
where ROWNUM <=
:MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
Now with a real example (gets rows 148, 149 and 150):
现在举一个真实的例子（获取第 148、149 和 150 行）：
select *
from
(select a.*, rownum rnum
from
(select id, data
from t
order by id, rowid) a
where rownum <= 150
)
where rnum >= 148;
回答by Pankaj Agarwal
You can also do using CTE with clause.
您也可以使用 CTE with 子句。
WITH maps AS (Select ROW_NUMBER() OVER (ORDER BY Id) AS rownum,*
from maps006 )
SELECT rownum, * FROM maps WHERE rownum >49 and rownum <101
回答by subrahmanyam j
select *
from emp
where rownum <= &upperlimit
minus
select *
from emp
where rownum <= &lower limit ;
回答by Ghyath Serhal
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY Id) AS RowNum, * FROM maps006) AS DerivedTable
WHERE RowNum BETWEEN 49 AND 101
