Mysql实用SQL例子 - 2

查询每个部门薪水最高的5个人

使用 LATERAL 实现
select d.dept_id, d.dept_name , e.emp_name, e.salary
from department d
left join lateral(
select e.*
from employee e
where e.dept_id = d.dept_id
order by e.salary desc
limit 5
) e
on e.dept_id = d.dept_id
where e.emp_name is not null
order by d.dept_id , e.salary desc;

使用窗口函数实现
SELECT t.dept_name, t.emp_name, t.salary
FROM (SELECT d.dept_name, e.emp_name, e.salary,
row_number() OVER (PARTITION BY d.dept_id ORDER BY e.salary DESC) AS rn
FROM department d
LEFT JOIN employee e
ON e.dept_id = d.dept_id) AS t
WHERE t.rn <= 5 and t.emp_name is not null
ORDER BY t.dept_name, t.salary;