【每日SQL打卡】Day1｜部门工资最高的员工【难度中等】

1、group by、max()

select
e.dept_no,e.emp_no,Max(s.salary)
from
dept_emp e , salaries s
where  e.emp_no = s.emp_no
group by e.dept_no

2、group by、order by 、limit
分组、排序、分页

select
e.dept_no,e.emp_no,s.salary
from
dept_emp e , salaries s
where  e.emp_no = s.emp_no
group by e.dept_no
ORDER by s.salary
limit 1

3、窗口函数
row_number() over()分组排序
partition by分组
order by排序

SELECT t.dept_no,t.emp_no,t.salary from 
(SELECT 
d.dept_no,d.emp_no,s.salary,
ROW_NUMBER() over(partition by d.dept_no order by s.salary desc) rn
from dept_emp d ,salaries s 
where d.emp_no = s.emp_no) as t 

where t.rn=1