如何用分析函数找出EMP表中每个部门工资最高的员工

概述EMP表是Oracle测试账户SCOTT中的一张雇员表，首先，我们来看看emp表的数据 其中，empno是员工编号，同时也是该表的主键，ename是员工姓名，sal是员工工资，deptno是员工部门。

EMP表是Oracle测试账户SCott中的一张雇员表，首先，我们来看看emp表的数据

sql> select * from emp;EMPNO Ename      JOB              MGR HIREDATE         SAL       COMM     DEPTNO----- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH      CLERK           7902 17-DEC-80        800                    20 7499 ALLEN      SALESMAN        7698 20-FEB81       1600       300          30 7521 WARD       SALESMAN        221250       500          7566 JOnes      MANAGER         7839 02-APR2975                    7654 MARTIN     SALESMAN        28-SEP1250      1400          7698 BLAKE      MANAGER         01-MAY2850                    7782 CLARK      MANAGER         09-JUN2450                    10 7788 SCott      ANALYST         7566 1987       3000                    7839 KING       PRESIDENT            -NOV5000                    7844 TURNER     SALESMAN        081500         0          7876 AdamS      CLERK           7788 231100                    7900 JAMES      CLERK           0381        950                    7902 FORD       ANALYST         7934 MILLER     CLERK           7782 -JAN82       1300                    1014 rows selected.

其中，empno是员工编号，同时也是该表的主键，ename是员工姓名，sal是员工工资，deptno是员工部门。

如何找出每个部门的最高工资的员工信息呢？

常用的方法是关联查询，SQL语句如下：

select emp.deptno,ename,sal emp,(select deptno,max(sal)maxsal from emp group by deptno) twhere emp.deptno=t.deptno and emp.sal=t.maxsal;

结果如下：

    DEPTNO Ename             SAL-------- ---------- ----------        30 BLAKE            2850        20 SCott            3000        10 KING             5000        20 FORD             3000

下面我们来看看执行计划：

Execution Plan--------------------------------------------------------Plan hash value: 269884559---------------------------------------------------------------------------| ID  | Operation            | name   | Rows   | Bytes  | Cost (%cpu) | Time     ||   0 | SELECT STATEMENT     |        |      3 |    117 7  (15)| 00:01 ||*  1 |  HASH JOIN           2 |   VIEW               |     78 4  (25)|    HASH GROUP BY     21 4 |     table ACCESS FulL| EMP    14 98 3   (0)5 FulL  182 ---------------------------------------------------------------------------Predicate information (IDentifIEd  operation ID):-------------------------------------------------   - access("EMP"."DEPTNO"="T"."DEPTNO" AND "EMP"."SAL"="T"."MAXSAL")Statistics--------------------------------------------------------      0  recursive calls        db block gets     13  consistent gets        physical reads        redo size    625  bytes sent via sql*Net to clIEnt    419  bytes received via sql clIEnt      2  sql*Net roundtrips to/  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 BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FolLOWING)maxsalFROM 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       285014 rows selected.

不难看出，sal等于maxsal的行即为每个部门最高工资的员工,下面用嵌套子查询得到目标结果。

SELECT deptno,sal FROM (       SELECT deptno                    sal                   ROWS        FROM emp) WHERE sal=maxsal;

输出结果如下：

2850

下面我们来看看该语句的执行计划：

Execution 4130734685--------------------------------------------------------------------------| name  | Rows  | Bytes %cpu)| Time       |       644 |  VIEW                |   WINDOW SORT        |    FulL |  EMP  --------------------------------------------------------------------------- filter("SAL""MAXSAL")  db block gets      6619  bytes sent via sql14  rows processed

可见，引入了分析函数以后，成本和逻辑读都减少了一半。

通过查询的结果，我们可以看出，20号部门有两个人的工资最高，有时候，我们只想得到一个人的信息，如何实现呢？

在这里我们会用到分析函数LAG，具体sql如下：

OVER(BY deptno) presal  deptno               sal              ROWS     DEPTNO Ename             SAL     PRESAL3000

剔除sal等于presal的行

 (        (               deptno                      sal                     ROWS  UNBOUNDED FolLOWING)maxsal                emp)        =maxsal) WHERE sal <> presal or presal is null;

输出结果如下：

2850

总结：

在实际生产环境中，此类应用还是蛮多的，譬如如何查询每个时段耗时最大的工单。当然，通过上述演示，我们也看出了group by函数的局限性。

关于LAST_VALUE和LAG函数的具体应用及说明，可参考Oracle官方文档：

1. LAST_VALUE

2. LAG

 

总结

以上是内存溢出为你收集整理的如何用分析函数找出EMP表中每个部门工资最高的员工全部内容，希望文章能够帮你解决如何用分析函数找出EMP表中每个部门工资最高的员工所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。