解法一 join 连接
1、两张表连接,group by 查询每个部门对应的最高薪水
select a.dept_no,max(b.salary) as maxSalary from dept_emp as a join salaries as b on a.emp_no=b.emp_no group by dept_no2、1表与salaries表连接,查询最高薪水对应的员工
select c.dept_no,d.emp_no,c.maxSalary from (select a.dept_no,max(b.salary) as maxSalary from dept_emp as a join salaries as b on a.emp_no=b.emp_no group by dept_no ) as c join salaries as d on c.maxSalary=d.salary order by c.dept_no;解法二 窗口函数 row_number 排序
1、两个表连接,按部门的薪水排序
select a.dept_no,a.emp_no,b.salary, row_number() over (partition by a.dept_no order by b.salary desc) as rnk from dept_emp as a join salaries as b on a.emp_no=b.emp_no2、1表中筛选排第一位的用工信息
select dept_no,emp_no,salary as maxSalary from (select a.dept_no,a.emp_no,b.salary, row_number() over (partition by a.dept_no order by b.salary desc) as rnk from dept_emp as a join salaries as b on a.emp_no=b.emp_no) as c where rnk=1;解法三 窗口函数 max()
1、两个表连接,新增一列每个部门最高薪水
select a.dept_no,a.emp_no,b.salary, max(b.salary) over (partition by a.dept_no) as maxsalary from dept_emp as a join salaries as b on a.emp_no=b.emp_no2、1表中摘选薪水等于最高薪水的员工信息
select dept_no,emp_no,salary as maxsalary from (select a.dept_no,a.emp_no,b.salary, max(b.salary) over (partition by a.dept_no) as maxsalary from dept_emp as a join salaries as b on a.emp_no=b.emp_no) as c where salary=maxsalary;解法四 可以用with 函数把以上三种方法第一步生成的表格创建为临时表,再进行后续的步骤
以解法三为例
1、创建临时表
with tmp_table as (select a.dept_no,a.emp_no,b.salary, max(b.salary) over (partition by a.dept_no) as maxsalary from dept_emp as a join salaries as b on a.emp_no=b.emp_no)2、在临时表中查询薪水等于最高薪水的员工信息
with tmp_table as (select a.dept_no,a.emp_no,b.salary, max(b.salary) over (partition by a.dept_no) as maxsalary from dept_emp as a join salaries as b on a.emp_no=b.emp_no) select dept_no,emp_no,salary as maxSalary from tmp_table where salary=maxsalary;