选择没有ROWNUM的前N行? [英] SELECTing top N rows without ROWNUM?
问题描述
希望您能帮我做作业:)
I hope you can help me with my homework :)
我们需要构建一个查询，以输出前N名收入最高的员工.
We need to build a query that outputs the top N best paid employees.
我的版本运行正常.
例如前3名:
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
;
请注意，这将输出前3名且工资相同的员工.
Note that this will output employees that are in the top 3 and have the same salary, too.
1:迈克(4080)
2:史蒂夫，2800
2:苏珊，2800
2:杰克，2800
3:克洛伊(1400)
1: Mike, 4080
2: Steve, 2800
2: Susan, 2800
2: Jack, 2800
3: Chloe, 1400
但是现在我们的老师不允许我们使用ROWNUM.
我搜索了很多东西，没有发现任何有用的东西.
But now our teacher does not allow us to use ROWNUM.
I searched far and wide and didn't find anything useable.
我的第二个解决方案，这要归功于贾斯汀·凯夫斯(Justin Caves)的提示.
My second solution thanks to Justin Caves' hint.
首先，我尝试了这个:
SELECT name, salary, ( rank() OVER ( ORDER BY salary DESC ) ) as myorder
FROM staff
WHERE myorder <= 3
;
错误消息为:"myorder:无效标识符"
The errormessage is: "myorder: invalid identifier"
感谢DCookie现在清楚了:
Thanks to DCookie its now clear:
"[...]分析之后应用 对where子句进行求值，其中 这就是为什么您会得到myorder的错误 是无效的标识符."
"[...] Analytics are applied AFTER the where clause is evaluated, which is why you get the error that myorder is an invalid identifier."
将SELECT换行即可解决此问题:
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.
第三种解决方案.
如果分析函数也 不允许，我可以选择的另一种选择 想象-一个你永远不会 曾经，曾经实际写过， 就像"
"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
推荐答案
由于这是家庭作业，因此提示而不是答案.您将要使用分析功能. ROW_NUMBER，RANK或DENSE_RANK可以工作，具体取决于您处理领带的方式.
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
关于性能，我不会依赖查询计划中的COST数-这只是一个估计，通常无法比较不同SQL语句的计划之间的成本.您最好查看查询实际执行的一致性获取数之类的问题，并考虑随着表中行数的增加查询性能将如何扩展.第三个选项的效率将根本不如其他两个选项高，这仅仅是因为它需要扫描STAFF表两次.
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.
我没有您的STAFF表，所以我将使用SCOTT模式中的EMP表
I don't have your STAFF table, so I'll use the EMP table from the SCOTT schema
解析功能解决方案与ROWNUM解决方案实际上实现了7个一致的获取
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
然而，COUNT(*)解决方案实际上执行了99次一致的获取，并且必须对表进行完整扫描两次，因此效率降低了10倍以上.随着表中行数的增加，伸缩性会更差
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
这篇关于选择没有ROWNUM的前N行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
