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

广801

本系列文章导航
　　[Oracle]高效的SQL语句之分析函数(一)--sum()
　　[Oracle]高效的SQL语句之分析函数(二)--max()
　　[Oracle]高效的SQL语句之分析函数(三)--row_number() /rank()/dense_rank()
　　[Oracle]高效的SQL语句之分析函数(四)--lag()/lead()
　　如果我们按照示例想得到每个部门薪水值最高的雇员的纪录，可以有四种方法实现：
　　先创建示例表
createtableemp
as
select*fromscott.emp;

altertableemp
addconstraintemp_pk
primarykey(empno);

createtabledept
as
select*fromscott.dept;

altertabledept
addconstraintdept_pk
primarykey(deptno);

　　
方法1.emp中的每一行都会进行max比较，费时
select*fromempemp1whereemp1.sal=(selectmax(emp2.sal)fromempemp2whereemp2.deptno=emp1.deptno)

　　
方法2.先子查询查找出max sal，然后与emp表相关联，如果逻辑复杂会产生较多代码
select*fromempemp1,(selectdeptno,max(sal)maxsalfromempemp2groupbyemp2.deptno)emp3 whereemp1.deptno=emp3.deptnoandemp1.sal=emp3.maxsal

　　
方法3.使用max分析函数
selectdeptno,maxsal,empnofrom(
selectmax(sal)over(partitionbydeptno)maxsal,emp.*fromemp)emp2
whereemp2.sal=emp2.maxsal

　　
方法4.使用dense_rank分析函数，如果一个部门可能存在多笔最大薪水，就不能使用row_number()分析函数
selectdeptno,sal,empnofrom(
selectemp.*,DENSE_RANK()over(partitionbydeptnoorderbysaldesc)rownumberfromemp)emp2
whererownumber=1

　　结果如下：
105000.007839
203000.007788
203000.007902
302850.007698

　　
　　博文来源：http://blog.csdn.net/huanghui22/archive/2007/05/03/1595161.aspx