大佬教程收集整理的这篇文章主要介绍了如何用分析函数找出EMP表中每个部门工资最高的员工，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。
EMP表是Oracle测试账户SCott中的一张雇员表，首先，我们来看看emp表的数据
sql> SELEct @H_696_8@* from emp; empno ename JOB MGR hiredate SAL COMM DEPTNO ----- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17@H_696_8@-DEC@H_696_8@-80 800 20 7499 ALLEN SALEsmaN 7698 20@H_696_8@-FEB81 1600 300 30 7521 WARD SALEsmaN 221250 500 7566 JOnes MANAGER 7839 02@H_696_8@-APR2975 7654 MARTin SALEsmaN 28@H_696_8@-SEP1250 1400 7698 BLAKE MANAGER 01@H_696_8@-@H_727_5@mAY2850 7782 CLARK MANAGER 09@H_696_8@-JUN2450 10 7788 SCott ANALYST 7566 1987 3000 7839 KING PRESIDENT @H_696_8@-NOV5000 7844 TURNER SALEsmaN 081500 0 7876 AdamS CLERK 7788 231100 7900 JAMES CLERK 0381 950 7902 FORD ANALYST 7934 MILLER CLERK 7782 @H_696_8@-JAN82 1300 10 14 rows SELEcted.
其中，empno是员工编号，同时也是该表的主键，ename是员工姓名，sal是员工工资，deptno是员工部门。
如何找出每个部门的最高工资的员工信息呢？
常用的方法是关联查询，sql语句如下：
SELEct emp.deptno,ename,sal emp,(SELEct deptno,@H_960_172@max(sal)maxsal from emp group by deptno) t where emp.deptno@H_696_8@=t.deptno @H_696_8@and emp.sal@H_696_8@=t.maxsal;
结果如下：
DEPTNO ename SAL -------- ---------- ---------- 30 BLAKE 2850 20 SCott 3000 10 KING 5000 20 FORD 3000
下面我们来看看执行计划：
Execution Plan -------------------------------------------------------- Plan hash value: 269884559 --------------------------------------------------------------------------- @H_696_8@| ID @H_696_8@| Operation @H_696_8@| name @H_696_8@| Rows @H_696_8@| Bytes @H_696_8@| Cost (@H_696_8@%cpu) @H_696_8@| Time @H_696_8@| | 0 @H_696_8@| SELECT STATEMENT @H_696_8@| @H_696_8@| 3 @H_696_8@| 117 7 (15)@H_696_8@| 00:01 @H_696_8@| @H_696_8@|* 1 @H_696_8@| HASH @H_696_8@JOIN 2 @H_696_8@| VIEW @H_696_8@| 78 4 (25)@H_696_8@| HASH GROUP BY 21 4 @H_696_8@| table ACCESS FulL@H_696_8@| EMP 14 98 3 (0)5 @H_696_8@FulL 182 @H_696_8@--------------------------------------------------------------------------- PreDicate information (IDentifIEd operation ID): ------------------------------------------------- @H_696_8@- access("EMP"."DEPTNO"@H_696_8@="T"."DEPTNO" @H_696_8@AND "EMP"."SAL"@H_696_8@="T"."MAXSAL") Statistics -------------------------------------------------------- 0 recursive calls @H_696_8@ db block gets 13 consistent gets @H_696_8@ physical reads @H_696_8@ redo size 625 bytes sent via sql@H_696_8@*Net to clIEnt 419 bytes received via sql@H_696_8@ clIEnt 2 sql@H_696_8@*Net roundtrips to@H_696_8@/ sorts (memory) 0 sorts (disk) 4 rows processed
不难看出，该查询针对同一个表走了两次全盘扫描，成本为7，逻辑读为13。
如何对上述查询进行优化呢？在这里，用到分析函数LAST_VALUE，LAST_VALUE返回排序集中的最后一个值。
SELECT deptno,sal,LAST_VALUE(sal) OVER(PARTITION BY deptno ORDER sal ROWS @H_696_8@betweeN UNBOUNDED PRECEDING @H_696_8@AND UNBOUNDED FolLOWING)maxsal FROM emp;
输出结果如下：
DEPTNO ename SAL MAXSAL -------- ---------- ---------- ---------- 10 MILLER 1300 10 CLARK 2450 5000 20 SMITH 800 20 AdamS 1100 20 JOnes 2975 3000 30 JAMES 950 30 MARTin 30 WARD 30 TURNER 1500 30 ALLEN 2850 2850 14 rows SELEcted.
不难看出，sal等于maxsal的行即为每个部门最高工资的员工,下面用嵌套子查询得到目标结果。
SELECT deptno,sal FROM ( SELECT deptno sal ROWS @H_696_8@ FROM emp) WHERE sal@H_696_8@=@H_727_5@maxsal;
输出结果如下：
2850
下面我们来看看该语句的执行计划：
Execution 4130734685 -------------------------------------------------------------------------- @H_696_8@| name @H_696_8@| Rows @H_696_8@| Bytes @H_696_8@%cpu)@H_696_8@| Time @H_696_8@| 644 @H_696_8@| VIEW @H_696_8@| WINDOW SORT @H_696_8@| FulL @H_696_8@| EMP @H_696_8@-------------------------------------------------------------------------- - filter("SAL"@H_696_8@"MAXSAL") db block gets 6619 bytes sent via sql14 rows processed
可见，引入了分析函数以后，成本和逻辑读都减少了一半。
通过查询的结果，我们可以看出，20号部门有两个人的工资最高，有时候，我们只想得到一个人的信息，如何实现呢？
在这里我们会用到分析函数LAG，具体sql如下：
剔除sal等于presal的行
( ( deptno sal ROWS @H_696_8@ UNBOUNDED FolLOWING)maxsal emp) =@H_727_5@maxsal) WHERE sal @H_696_8@<> presal @H_696_8@or presal is null;
输出结果如下：
2850
总结：
在实际生产环境中，此类应用还是蛮多的，譬如如何查询每个时段耗时最大的工单。当然，通过上述演示，我们也看出了group by函数的局限性。
关于LAST_VALUE和LAG函数的具体应用及说明，可参考Oracle官方文档：
1. LAST_VALUE
2. LAG
以上是大佬教程为你收集整理的如何用分析函数找出EMP表中每个部门工资最高的员工全部内容，希望文章能够帮你解决如何用分析函数找出EMP表中每个部门工资最高的员工所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。