选择没有 ROWNUM 的前 N 行？

发布于 2024-11-01 07:15:35 字数 1491 浏览 5 评论 0原文

我希望你能帮我做作业 :)

我们需要构建一个查询来输出前 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
;

请注意，这将输出前 3 名且薪资相同的员工。

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

原文

I hope you can help me with my homework :)

We need to build a query that outputs the top N best paid employees.

My version works perfectly fine.
For example the top 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
;

Note that this will output employees that are in the top 3 and have the same salary, too.

1: Mike, 4080
2: Steve, 2800
2: Susan, 2800
2: Jack, 2800
3: Chloe, 1400

But now our teacher does not allow us to use ROWNUM.
I searched far and wide and didn't find anything useable.

My second solution thanks to Justin Caves' hint.

First i tried this:

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

The errormessage is: "myorder: invalid identifier"

Thanks to DCookie its now clear:

"[...] Analytics are applied AFTER
the where clause is evaluated, which
is why you get the error that myorder
is an invalid identifier."

Wrapping a SELECT around solves this:

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

My teacher strikes again and don't allow such exotic analytic functions.

3rd solution from @Justin Caves.

"If analytic functions are also
disallowed, the other option I could
imagine-- one that you would never,
ever, ever actually write in practice,
would be something like"

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

成熟的代价 2024-11-08 07:15:35

由于这是作业，因此是提示而不是答案。您将需要使用分析函数。 ROW_NUMBER、RANK 或 DENSE_RANK 可以发挥作用，具体取决于您想要如何处理关系。

如果分析函数也被禁止，我可以想象的另一种选择——一个你在实践中永远不会真正编写的选项，就像

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

关于性能，我不会依赖查询中的成本数字计划——这只是一个估计，通常不可能比较不同 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

Since this is homework, a hint rather than an answer. You'll want to use analytic functions. ROW_NUMBER, RANK, or DENSE_RANK can work depending on how you want to handle ties.

If analytic functions are also disallowed, the other option I could imagine-- one that you would never, ever, ever actually write in practice, would be something like

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

With regard to performance, I wouldn't rely on the COST number from the query plan-- that's only an estimate and it is not generally possible to compare the cost between plans for different SQL statements. You're much better off looking at something like the number of consistent gets the query actually does and considering how the query performance will scale as the number of rows in the table increases. The third option is going to be radically less efficient than the other two simply because it needs to scan the STAFF table twice.

I don't have your STAFF table, so I'll use the EMP table from the SCOTT schema

The analytic function solution actually does 7 consistent gets as does the ROWNUM solution

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

The COUNT(*) solution, however, actually does 99 consistent gets and has to do a full scan of the table twice so it is more than 10 times less efficient. And it will scale much worse as the number of rows in the table increases

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

回复收藏 0 原文

清风夜微凉 2024-11-08 07:15:35

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

回复收藏 0 原文

吃不饱 2024-11-08 07:15:35

甲骨文？那么窗口函数呢？

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

Oracle? What about window functions?

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

回复收藏 0 原文

硪扪都還晓 2024-11-08 07:15:35

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

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

When you use count(distinct <exp>), equal ranking top salaries will be treated as tie ranks.

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

回复收藏 0 原文

我还不会笑 2024-11-08 07:15:35

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

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

（FETCH FIRST 语法是 Oracle 12c 中的新语法）

You could solve this in Oracle 12c

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

(FETCH FIRST syntax is new with Oracle 12c)

回复收藏 0 原文

~没有更多了~

关于作者

怪我入戏太深

暂无简介

0 文章

0 评论

24 人气

关注发私信

友情链接

文江博客

选择没有 ROWNUM 的前 N 行？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（5）

关于作者

相关话题

热门标签

推荐作者

Gabu-gabumon

qq_CgiN62

荔枝明

赏烟花じ飞满天

独守阴晴ぅ圆缺

¤→小豸慧

友情链接

选择没有 ROWNUM 的前 N ​​行？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（5）

关于作者

相关话题

热门标签

推荐作者

Gabu-gabumon

qq_CgiN62

荔枝明

赏烟花じ飞满天

独守阴晴ぅ圆缺

¤→小豸慧

友情链接

选择没有 ROWNUM 的前 N 行？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。