[Oracle]高效的SQL语句之分析函数(二)--max()

如果我们按照示例想得到每个部门薪水值最高的雇员的纪录，可以有四种方法实现：

先创建示例表

create   table  emp
as
select   *   from  scott.emp;

alter   table  emp
add   constraint  emp_pk
primary   key (empno);

create   table  dept
as
select   *   from  scott.dept;

alter   table  dept
add   constraint  dept_pk
primary   key (deptno);

方法1.emp中的每一行都会进行max比较，费时

select   *   from  emp emp1  where  emp1.sal = ( select   max (emp2.sal)  from  emp emp2  where  emp2.deptno = emp1.deptno)

方法2.先子查询查找出max sal，然后与emp表相关联，如果逻辑复杂会产生较多代码

   select   *   from  emp emp1,( select  deptno, max (sal) maxsal  from  emp emp2  group   by  emp2.deptno) emp3 where  emp1.deptno = emp3.deptno  and  emp1.sal = emp3.maxsal

方法3.使用max分析函数

select  deptno,maxsal,empno  from (
  select   max (sal)  over  (partition  by  deptno) maxsal,emp. *   from  emp) emp2
  where  emp2.sal = emp2.maxsal

方法4.使用dense_rank分析函数，如果一个部门可能存在多笔最大薪水，就不能使用row_number()分析函数

select  deptno,sal,empno  from ( 
  select  emp. * ,DENSE_RANK()  over  (partition  by  deptno  order   by  sal  desc ) rownumber  from  emp) emp2
  where  rownumber = 1  

结果如下：

10      5000.00      7839
20      3000.00      7788
20      3000.00      7902
30      2850.00      7698