选择没有ROWNUM的前N行？

| 希望您能帮我做作业：）我们需要构建一个查询，以输出前N名最高薪雇员。我的版本运行正常。例如前3名：

SELECT name, salary
FROM staff
WHERE salary IN ( SELECT * 
                  FROM ( SELECT salary
                         FROM staff 
                         ORDER BY salary DESC ) 
                  WHERE ROWNUM <= 3 )
ORDER BY salary DESC
;

请注意，这将输出前三名且工资相同的员工。 1：迈克（4080） 2：史蒂夫，2800 2：苏珊，2800 2：杰克，2800 3：克洛伊（1400）但是现在我们的老师不允许我们使用ROWNUM。我到处搜寻，没有发现任何可用的内容。我的第二个解决方案要感谢Justin Caves的提示。首先我尝试了这个：

SELECT name, salary, ( rank() OVER ( ORDER BY salary DESC ) ) as myorder
FROM staff
WHERE myorder <= 3
;

错误消息是：\“ myorder：无效的标识符\” 多亏了DCookie，它现在很清楚了： \“ [...]分析之后应用计算where子句，其中这就是为什么您会得到myorder的错误是无效的标识符。\“ 将SELECT包装起来即可解决此问题：

SELECT *
FROM ( SELECT name, salary, rank() OVER ( ORDER BY salary DESC ) as myorder FROM staff )
WHERE myorder <= 3
;

我的老师再次罢工，不允许使用这种奇特的分析功能。 @Justin Caves的第三个解决方案。 \“如果解析函数也不允许，我可以选择另一个选项想象-一个你永远不会曾经，曾经实际写过，就像\“

SELECT name, salary
  FROM staff s1
 WHERE (SELECT COUNT(*)
          FROM staff s2
         WHERE s1.salary < s2.salary) <= 3

已邀请:

5 个回复

版萍层分

由于这是家庭作业，因此是提示而不是答案。您将要使用分析功能。 ROW_NUMBER，RANK或DENSE_RANK可以工作，具体取决于您处理领带的方式。如果也不允许使用解析函数，那么我可以想象的另一种选择-您永远不会在实践中实际写过的东西，就像

SELECT name, salary
  FROM staff s1
 WHERE (SELECT COUNT(*)
          FROM staff s2
         WHERE s1.salary < s2.salary) <= 3

关于性能，我不会依赖查询计划中的COST号码，这只是一个估计，通常无法比较不同SQL语句的计划之间的成本。您最好查看查询实际执行的一致性获取数量，并考虑随着表中行数增加查询性能将如何扩展。第三个选项的效率将根本不如其他两个选项高，这仅仅是因为它需要扫描STAFF表两次。我没有您的STAFF表，所以我将使用SCOTT模式中的EMP表解析函数解决方案与ROWNUM解决方案实际上进行7个一致的获取

Wrote file afiedt.buf

  1  select ename, sal
  2    from( select ename,
  3                 sal,
  4                 rank() over (order by sal) rnk
  5            from emp )
  6*  where rnk <= 3
SQL> /

ENAME             SAL
---------- ----------
smith             800
SM0               950
ADAMS            1110


Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077

--------------------------------------------------------------------------------
-
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
|   0 | SELECT STATEMENT         |      |    14 |   672 |     4  (25)| 00:00:01
|*  1 |  VIEW                    |      |    14 |   672 |     4  (25)| 00:00:01
|*  2 |   WINDOW SORT PUSHED RANK|      |    14 |   140 |     4  (25)| 00:00:01
|   3 |    TABLE ACCESS FULL     | EMP  |    14 |   140 |     3   (0)| 00:00:01
--------------------------------------------------------------------------------
-

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(\"RNK\"<=3)
   2 - filter(RANK() OVER ( ORDER BY \"SAL\")<=3)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        668  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL> select ename, sal
  2    from( select ename, sal
  3            from emp
  4           order by sal )
  5   where rownum <= 3;

ENAME             SAL
---------- ----------
smith             800
SM0               950
ADAMS            1110


Execution Plan
----------------------------------------------------------
Plan hash value: 1744961472

--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     3 |   105 |     4  (25)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |      |       |       |            |          |
|   2 |   VIEW                  |      |    14 |   490 |     4  (25)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|      |    14 |   140 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | EMP  |    14 |   140 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=3)
   3 - filter(ROWNUM<=3)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        668  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

但是，COUNT（*）解决方案实际上执行了99次一致的获取，并且必须对表进行完全扫描两次，因此效率降低了10倍以上。随着表中行数的增加，伸缩性将大大恶化

SQL> select ename, sal
  2    from emp e1
  3   where (select count(*) from emp e2 where e1.sal < e2.sal) <= 3;

ENAME             SAL
---------- ----------
JONES            2975
SCOTT            3000
KING             5000
FORD             3000
FOO


Execution Plan
----------------------------------------------------------
Plan hash value: 2649664444

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   140 |    24   (0)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | EMP  |    14 |   140 |     3   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  4 |    TABLE ACCESS FULL| EMP  |     1 |     4 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( (SELECT COUNT(*) FROM \"EMP\" \"E2\" WHERE
              \"E2\".\"SAL\">:B1)<=3)
   4 - filter(\"E2\".\"SAL\">:B1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         99  consistent gets
          0  physical reads
          0  redo size
        691  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

辰炔诚薯

您必须用另一条选择包装该语句的原因是，外部选择一条语句将结果集限制为所需的行号。这是有关分析的有用链接。如果您自己运行内部选择，您将明白为什么必须这样做。在评估where子句之后应用Analytics（分析），这就是为什么您收到myorder是无效标识符的错误的原因。

珊畴炮贩号

甲骨文？窗口功能呢？

select * from 
(SELECT s.*, row_number over (order by salary desc ) as rn FROM staff s )
where rn <=3

荆怖赡

当您使用count(distinct <exp>)时，排名相同的最高薪水将被视为平局。

select NAME, SALARY
from  STAFF STAFF1
where 3 >= ( select count(distinct STAFF2.SALARY) RANK
               from STAFF STAFF2
              where STAFF2.SALARY >= STAFF1.SALARY)

功飘

您可以在Oracle 12c中解决此问题

select NAME, SALARY
from  STAFF
order by SALARY DESC
FETCH FIRST 3 ROWS ONLY

（FETCH FIRST语法是Oracle 12c的新增功能）

要回复问题请先登录或注册

选择没有ROWNUM的前N行？

5 个回复

发起人

oracle

rownum

问题状态

选择没有ROWNUM的前N行？

与内容相关的链接

5 个回复

发起人

oracle

rownum

问题状态