题解 | #查找入职员工时间排名倒数第三的员工所有信息#

使用max函数写法，最容易想到的方法

select de.dept_no,de.emp_no,salary maxSalaty
from dept_emp de left join salaries s
on de.emp_no=s.emp_no
where salary=(select max(salary)
from dept_emp de1 left join salaries s
on de1.emp_no=s.emp_no where de1.dept_no=de.dept_no)
order by dept_no

工资第一高的通用写法

select de.dept_no,de.emp_no,salary from
 dept_emp de left join salaries s
on de.emp_no=s.emp_no where salary in
(select distinct(a.salary) from 
(select de.dept_no,de.emp_no,salary
from dept_emp de left join salaries s
on de.emp_no=s.emp_no)a,
(select de.dept_no,de.emp_no,salary
from dept_emp de left join salaries s
on de.emp_no=s.emp_no)b
where a.dept_no=b.dept_no and a.salary<=b.salary and de.dept_no=a.dept_no
group by a.salary
having count(distinct b.salary)=1)
order by dept_no

上述通用方法使用with的简便写法

with s as (select de.dept_no,de.emp_no,salary from
 dept_emp de left join salaries s
on de.emp_no=s.emp_no)
select s.dept_no,s.emp_no,s.salary maxSalary
from s
where s.salary in (select distinct(a.salary) from s a,s b
where a.dept_no=b.dept_no and a.dept_no=s.dept_no and a.salary<=b.salary
group by a.salary
having count(distinct b.salary)=1)
order by s.dept_no

更加巧妙的通用方法

select de.dept_no,de.emp_no,salary
from dept_emp de join salaries s on
de.emp_no=s.emp_no
where (
select count(distinct s1.salary) from dept_emp de1 join
salaries s1 on
de1.emp_no=s1.emp_no
where de1.dept_no=de.dept_no and s1.salary>=s.salary
)=1
order by de.dept_no

使用窗口函数的方法，作为练习题，不推荐使用这种方法，自己的思维得不到锻炼

select a.dept_no,a.emp_no,a.salary MaxSalary from 
(select de.dept_no,de.emp_no,s.salary,
dense_rank() over(partition by de.dept_no order by s.salary desc) as rank1
from dept_emp de,salaries s
where de.emp_no=s.emp_no) a
where a.rank1=1