需求,要求返回最高工资所在行的员工名称
SQL> select deptno,empno,
2 (select max(b.ename) from emp b where b.sal=a.max_sal) as max_sal_name,
3 ename,sal
4 from
5 (select deptno,empno,max(sal) over(partition by deptno) as max_sal,
6 ename,sal
7 from emp a) a
8 order by 1,5 desc;
DEPTNO EMPNO MAX_SAL_NA ENAME SAL
---------- ---------- ---------- ---------- ----------
10 7839 KING KING 5000
10 7782 KING CLARK 2450
10 7934 KING MILLER 1300
20 7788 SCOTT SCOTT 3000
20 7902 SCOTT FORD 3000
20 7566 SCOTT JONES 2975
20 7876 SCOTT ADAMS 1100
20 7369 SCOTT SMITH 800
30 7698 BLAKE BLAKE 2850
30 7499 BLAKE ALLEN 1600
30 7844 BLAKE TURNER 1500
30 7654 BLAKE MARTIN 1250
30 7521 BLAKE WARD 1250
30 7900 BLAKE JAMES 950
14 rows selected.
SQL>
原理:先求出工资最高的员工的工资,其次按照工资的最高值进行关联。
但是在ORACLE中有一个分析函数就可以直接找到这个值,非常方便
即MAX() KEEP (DENSE_RANK FIRST ORDER BY )
SQL> select deptno,empno,ename,sal,
2 max(ename) keep(dense_rank first order by sal) over(partition by deptno) as max_sal_name,
3 min(ename) keep(dense_rank last order by sal) over(partition by deptno) as min_sal_name
4 from emp
5 order by 1,4 desc;
DEPTNO EMPNO ENAME SAL MAX_SAL_NA MIN_SAL_NA
---------- ---------- ---------- ---------- ---------- ----------
10 7839 KING 5000 MILLER KING
10 7782 CLARK 2450 MILLER KING
10 7934 MILLER 1300 MILLER KING
20 7788 SCOTT 3000 SMITH FORD
20 7902 FORD 3000 SMITH FORD
20 7566 JONES 2975 SMITH FORD
20 7876 ADAMS 1100 SMITH FORD
20 7369 SMITH 800 SMITH FORD
30 7698 BLAKE 2850 JAMES BLAKE
30 7499 ALLEN 1600 JAMES BLAKE
30 7844 TURNER 1500 JAMES BLAKE
30 7654 MARTIN 1250 JAMES BLAKE
30 7521 WARD 1250 JAMES BLAKE
30 7900 JAMES 950 JAMES BLAKE
14 rows selected.
SQL>
为了更清晰可以加入sal一栏,如下:
SQL> select deptno,
2 min(sal) min_sal,
3 min(ename) keep(dense_rank last order by sal) as min_sal_name,
4 max(sal) max_sal,
5 max(ename) keep(dense_rank last first by sal) as max_sal_name
6 from emp
7 group by deptno;
DEPTNO MIN_SAL MIN_SAL_NA MAX_SAL MAX_SAL_NA
---------- ---------- ---------- ---------- ----------
10 1300 KING 5000 KING
20 800 FORD 3000 SCOTT
30 950 BLAKE 2850 BLAKE
SQL>
以上示例在first,last语句中,无论我们取最高工资还是最低工资,我们都是使用max,那么这个MAX到底有什么用?
看以下示例:
SQL> select deptno,empno,
2 max(sal) over(partition by deptno) max_sal,
3 ename,sal
4 from emp
5 order by 1,5 desc;
DEPTNO EMPNO MAX_SAL ENAME SAL
---------- ---------- ---------- ---------- ----------
10 7839 5000 KING 5000
10 7782 5000 CLARK 2450
10 7934 5000 MILLER 1300
20 7788 3000 SCOTT 3000
20 7902 3000 FORD 3000
20 7566 3000 JONES 2975
20 7876 3000 ADAMS 1100
20 7369 3000 SMITH 800
30 7698 2850 BLAKE 2850
30 7499 2850 ALLEN 1600
30 7844 2850 TURNER 1500
30 7654 2850 MARTIN 1250
30 7521 2850 WARD 1250
30 7900 2850 JAMES 950
14 rows selected.
SQL>
我们发现部门20中有两个工资相同最高的员工。单独拿出来看看:
SQL> select deptno,empno,
2 max(sal) over(partition by deptno) max_sal,
3 ename,sal
4 from emp
5 where deptno=20
6 order by 1,5 desc;
DEPTNO EMPNO MAX_SAL ENAME SAL
---------- ---------- ---------- ---------- ----------
20 7788 3000 SCOTT 3000
20 7902 3000 FORD 3000
20 7566 3000 JONES 2975
20 7876 3000 ADAMS 1100
20 7369 3000 SMITH 800
SQL>
然后我们使用fist和last来看一下。
SQL> select deptno,empno,ename,sal,
2 to_char(wmsys.wm_concat(ename) keep(dense_rank last order by sal) over(partition by deptno)) as max_sal_ename,
3 min(ename) keep(dense_rank last order by sal) over(partition by deptno) as max_sal_ename_min,
4 max(ename) keep(dense_rank last order by sal) over(partition by deptno) as max_sal_ename_max
5 from emp
6 where deptno=20
7 order by 1,4 desc;
DEPTNO EMPNO ENAME SAL MAX_SAL_ENAME MAX_SAL_EN MAX_SAL_EN
---------- ---------- ---------- ---------- --------------- ---------- ----------
20 7788 SCOTT 3000 SCOTT,FORD FORD SCOTT
20 7902 FORD 3000 SCOTT,FORD FORD SCOTT
20 7566 JONES 2975 SCOTT,FORD FORD SCOTT
20 7876 ADAMS 1100 SCOTT,FORD FORD SCOTT
20 7369 SMITH 800 SCOTT,FORD FORD SCOTT
SQL>
结论:可以看到,当最值有重复的数据的时候,keep()得到的是一个数据集(SCOTT,FORD),这个时候前面的聚合函数就会起作用,min()和max()分别得到FORD和SCOTT
说明:在Oracle10G中有wmsys函数,这个函数主要进行行转列,但是在11中Oracle默认不支持了,所以要执行用户解锁和脚本初始化才能使用,在ORACLE12C中这个函数怎么做目前还没有探究。
步骤
1、alter user wmsys account unlock;
2、@
ORACLEHOME/rdbms/admin/owmctab.plb;3、@
ORACLE_HOME/rdbms/admin/owmaggrs.plb;
4、@
ORACLEHOME/rdbms/admin/owmaggrb.plb5、@
ORACLE_HOME/rdbms/admin/owminst.plb
做完上面五个步骤就可以使用wmsys.wm_concat()函数了。