SQL查询之返回最值所在的行数据

来源：互联网发布：阿里云ecs上传文件编辑：程序博客网时间：2024/06/06 03:54

需求，要求返回最高工资所在行的员工名称

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         95014 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      BLAKE14 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 BLAKESQL>

以上示例在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         95014 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         800SQL>

然后我们使用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       SCOTTSQL>

结论：可以看到，当最值有重复的数据的时候，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()函数了。

阅读全文

0 0