嘿嘿--------------这是 我的第一次面试题.....
这道题 也看过 不过 怎么做 忘了
题目是这样的 有表 tab(name varchar2(20),id number pk,sal number)
要求求出第二高工资的员工.....
刚开始 有点愣住了 一直在想 我以前 看到过 它是怎么做来着...
后来没想到 就干脆用自己的思路做
我给出了 俩个答案 不过 都不知道是不是 最优的（这里只求出第二薪水）
1 select max(sal) from (select sal from tab minus select max(sal) from tab); 这个肯定不行 排好几次序
看看他的是如何执行的：
Plan hash value: 2782735200
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 8 (25)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 14 | 182 | 8 (25)| 00:00:01 |
| 3 | MINUS | | | | | |
| 4 | SORT UNIQUE | | 14 | 56 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
--------------------------------------------------
| 6 | SORT AGGREGATE | | 1 | 4 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
2 select min(sal) from (select sal from (select sal from tab order by sal desc) where rownum<3)
看看它是如何执行的：
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (25)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 2 | 26 | 4 (25)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 14 | 182 | 4 (25)| 00:00:01 |
|* 5 | SORT ORDER BY STOPKEY| | 14 | 56 | 4 (25)| 00:00:01 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
--------------------------------------------------
| 6 | TABLE ACCESS FULL | EMP | 14 | 56 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
这里cost是上一个的一半....
刚想出 第三种想法：
3 ,select sal from (select sal,rank() over(order by sal desc) di from tab) where di=2;
看看它的 执行计划：
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 14 | 364 | 4 (25)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK | | 14 | 56 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 56 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
这个也还不错...
在看看他给我的 我以前看到过的 不过忘了 嘿嘿
select max(sal) from (select sal from tab t where t.sal <>(select max(sal) from tab));
看看 他的执行计划：
Plan hash value: 621202636
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL | EMP | 13 | 52 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 4 | | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
cost 6！！尽然比我的想的其中一个要多...
后面补充的：
select sal from (select sal,rownum no from (select sal from scott.emp order by sal desc)) where no=2
嘿嘿 我太强大了....
开玩笑
仅供学习参考