有一个员工表dept_emp简况如下:
有一个薪水表salaries简况如下:
获取所有部门中员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按照部门编号升序排列,以上例子输出如下:
解法1:
SELECT d1.dept_no, d1.emp_no, s1.salary
FROM dept_emp as d1
INNER JOIN salaries as s1
ON d1.emp_no=s1.emp_no
AND d1.to_date='9999-01-01'
AND s1.to_date='9999-01-01'//将两表联结起来,外面的表确定一个部门
WHERE s1.salary in (//薪水在下面表中,里面的表进行子查询,找薪水的最大值
SELECT MAX(s2.salary)
FROM dept_emp as d2
INNER JOIN salaries as s2
ON d2.emp_no=s2.emp_no
AND d2.to_date='9999-01-01'
AND s2.to_date='9999-01-01'
AND d2.dept_no = d1.dept_no
)
ORDER BY d1.dept_no;
解法2:(利用开窗函数)
select t.dept_no,t.emp_no,t.salary from
(
select
de.dept_no,de.emp_no,s.salary,
row_number() over(partition by de.dept_no order by s.salary desc) as rk//对分区dept_no内的数据按salary排序(同一分区,相同值,不同序,不同分区,相同值,不同序)
from (
select * from dept_emp where to_date='9999-01-01') de
inner join
(select * from salaries where to_date='9999-01-01') s on de.emp_no=s.emp_no
)t
where t.rk=1;
在两个表联结的新表t中,利用row_number()函数,对不同的dept_no分区进行salary排序,每个分区最大的就是每个部门最大的工资