SQL ROWNUM如何返回特定范围之间的行

Question

如何返回特定范围的ROWNUM值？

我正在尝试以下方法：

select * from maps006 where rownum >49 and rownum <101

这仅返回与<运算符匹配的行。

Answer 1

&nbsp;SELECT * from&nbsp;(&nbsp;select m.*, rownum r&nbsp;from maps006 m&nbsp;)&nbsp;where r > 49 and r < 101

Answer 2

SELECT&nbsp; *FROM&nbsp; &nbsp; (&nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp; q.*, rownum rn&nbsp; &nbsp; &nbsp; &nbsp; FROM&nbsp; &nbsp; (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp; *&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM&nbsp; &nbsp; maps006&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ORDER BY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) q&nbsp; &nbsp; &nbsp; &nbsp; )WHERE&nbsp; &nbsp;rn BETWEEN 50 AND 100请注意双嵌套视图。ROWNUM在之前评估ORDER BY，因此需要正确编号。如果省略ORDER BY子句，则不会获得一致的顺序。

Answer 3

我知道这是一个老问题，但是在最新版本中提及新功能很有用。从Oracle 12c开始，您可以使用新的Top-n Row限制功能。无需编写子查询，不依赖于ROWNUM。例如，以下查询将按升序返回员工的最高薪水到第4高薪至第7高薪：SQL> SELECT empno, sal&nbsp; 2&nbsp; FROM&nbsp; &nbsp;emp&nbsp; 3&nbsp; ORDER BY sal&nbsp; 4&nbsp; OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;&nbsp; &nbsp; &nbsp;EMPNO&nbsp; &nbsp; &nbsp; &nbsp; SAL---------- ----------&nbsp; &nbsp; &nbsp; 7654&nbsp; &nbsp; &nbsp; &nbsp;1250&nbsp; &nbsp; &nbsp; 7934&nbsp; &nbsp; &nbsp; &nbsp;1300&nbsp; &nbsp; &nbsp; 7844&nbsp; &nbsp; &nbsp; &nbsp;1500&nbsp; &nbsp; &nbsp; 7499&nbsp; &nbsp; &nbsp; &nbsp;1600SQL>