Oracle 工资最高，但总体工资最高的部门应该是最高/第一

create table emp_sal_test ( dept# number ,
emp_name varchar2(200),
salary   number ) 
/

insert into emp_sal_test values ( 40 , 'Isha',80000);
insert into emp_sal_test values ( 40 , 'Pooja',70000);
insert into emp_sal_test values ( 40 , 'Geet',77000);
insert into emp_sal_test values ( 10 , 'Julie',75000);
insert into emp_sal_test values ( 10 , 'Jia',69000);
insert into emp_sal_test values ( 10 , 'Diya',72000);
insert into emp_sal_test values ( 20 , 'Sanvi',74000);
insert into emp_sal_test values ( 20 , 'Siya',65000);
insert into emp_sal_test values ( 20 , 'Seeta',81000);

The result of the query should be as following :
DEPT# EMP    SALARY 

20 Seeta 81000 
20 Sanvi 74000 
20 Siya 65000 

40 Isha 80000 
40      Geet    77000
40 Pooja 70000 

10 Julie 75000 
10 Diya 72000 
10 Jia 69000 
复制

select e.*, max(salary) over (partition by e.dept#) dept_mx_sal 
from   emp_sal_test e;

DEPT#  EMP_NAME  SALARY  DEPT_MX_SAL  
10     Jia       69000   75000        
10     Diya      72000   75000        
10     Julie     75000   75000        
20     Sanvi     74000   81000        
20     Seeta     81000   81000        
20     Siya      65000   81000        
40     Geet      77000   80000        
40     Pooja     70000   80000        
40     Isha      80000   80000
复制

select e.*, max(salary) over (partition by e.dept#) dept_mx_sal 
from   emp_sal_test e
order  by dept_mx_sal desc, salary desc

DEPT#  EMP_NAME  SALARY  DEPT_MX_SAL  
20     Seeta     81000   81000        
20     Sanvi     74000   81000        
20     Siya      65000   81000        
40     Isha      80000   80000        
40     Geet      77000   80000        
40     Pooja     70000   80000        
10     Julie     75000   75000        
10     Diya      72000   75000        
10     Jia       69000   75000 
复制