在Oracle 11g中选择两个值之间的行

Question

这是一个常见的问题，我在很多地方看到，但还不知道它是否可能。我试图使用工具在2到5之间选择行。

根据查询结果，应根据以下查询选择第3次和第4次查询。

SELECT * FROM MyTable
WHERE ROWNUM > 2 AND ROWNUM < 5

但不是选择第三排和第四排，

然后，我尝试了以下查询

SELECT * FROM MyTable
WHERE RN BETWEEN 2 AND 5

这在语法和程序上也是正确的，但不选择确切的列。

Answer 1

一点分析。

按$$$排序的EMP表中的薪资如下所示：

SQL> select ename, sal
  2  from emp
  3  order by sal;

ENAME             SAL
---------- ----------
SMITH             800
JAMES             950   2   you want to return James ...
WARD             1250   3
MARTIN           1250   4
MILLER           1300   5   ... to Miller
TURNER           1500
ALLEN            1600
CLARK            2450
BLAKE            2850
JONES            2975
FORD             3000
KING             5000

12 rows selected.

SQL>

如果你这样做，你就会得到你想要的：

SQL> select ename, sal, rn
  2  from (select ename, sal, row_number() over (order by sal) rn
  3        from emp
  4       )
  5  where rn between 2 and 5;

ENAME             SAL         RN
---------- ---------- ----------
JAMES             950          2
WARD             1250          3
MARTIN           1250          4
MILLER           1300          5

SQL>

然而，正如你所看到的，沃德和马丁赚到了同样的1250美元。那么，我们是否应该把他们算上相同的薪水，把特纳也包括在名单中呢？还有另外两个分析函数可能会帮助您决定：RANK和DENSE_RANK

SQL> select ename, sal,
  2    row_number() over (order by sal) rn,
  3    rank() over (order by sal) rnk,
  4    dense_rank() over (order by sal) drnk
  5  from emp
  6  order by sal;

ENAME             SAL         RN        RNK       DRNK
---------- ---------- ---------- ---------- ----------
SMITH             800          1          1          1
JAMES             950          2          2          2   2nd isn't questionable, but ...
WARD             1250          3          3          3
MARTIN           1250          4          3          3
MILLER           1300          5          5          4   ... which one is 5th? Miller (RN and RNK), ...
TURNER           1500          6          6          5   ... or Turner (DRNK column)?
ALLEN            1600          7          7          6
CLARK            2450          8          8          7
BLAKE            2850          9          9          8
JONES            2975         10         10          9
FORD             3000         11         11         10
KING             5000         12         12         11

12 rows selected.

SQL>

公平地说，在这种情况下，DENSE_RANK可能是最好的选择：

SQL> select ename, sal, drnk
  2  from (select ename, sal, dense_rank() over (order by sal) drnk
  3        from emp
  4       )
  5  where drnk between 2 and 5;

ENAME             SAL       DRNK
---------- ---------- ----------
JAMES             950          2
WARD             1250          3
MARTIN           1250          3
MILLER           1300          4
TURNER           1500          5

SQL>

现在你有几种选择，选择最适合你的。

Answer 2

使用子查询：

SELECT t.*
FROM (SELECT t.*, ROWNUM as rn
      FROM MyTable t
     ) t
WHERE rn > 2 AND rn < 5;

注意，表表示无序集。没有第一行或第二行这样的东西。您应该有一个ORDER BY子句来指定排序。

您的版本不能工作的原因是，当将第一行放入结果集中时，rownum从1开始。如果没有放入任何行，则值永远不会增加。所以，它永远不会击中2或3。

我还应该指出，SQL中的between是包含的。因此，>=和<=更合适。

编辑：

我应该注意到，Oracle 12+支持FETCH/OFFSET

select t.*
from mytable t
offset 2                  -- start on the third row
fetch first 2 rows only   -- fetch two rows in total

在这种情况下，仍然推荐使用order by。