[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