方法1:
1.依据每个部门对员工薪水排名--窗口函数
select dept_no,d.emp_no,salary
,rank()over(partition by dept_no order by salary desc) posn
from dept_emp d
inner join salaries s
on d.emp_no=s.emp_no
2.筛选出每个部门排名第一的记录
SELECT dept_no,emp_no,salary
from (
select dept_no,d.emp_no,salary
,rank()over(partition by dept_no order by salary desc) posn
from dept_emp d
inner join salaries s
on d.emp_no=s.emp_no
) as t1
where posn=1
order by dept_no
方法2:
1.统计每个部门的最高工资 --max
select dept_no,max(salary)
from dept_emp d
inner join salaries s
on d.emp_no=s.emp_no
group by dept_no
2.统计在该部门且工资最高的员工记录 --子查询
select dept_no,d.emp_no,salary
from dept_emp d
inner join salaries s
on d.emp_no=s.emp_no
where (dept_no,salary) in (
select dept_no,max(salary)
from dept_emp d
inner join salaries s
on d.emp_no=s.emp_no
group by dept_no
)
order by dept_no
有新方法欢迎评论区评论