组合连接和聚合函数易错例题
2021-02-22 本文已影响0人
龙眠散人
题目描述
有一个员工表dept_emp简况如下:
image.png有一个薪水表salaries简况如下:
image.png获取所有部门中员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按照部门编号升序排列,以上例子输出如下:
image.png
select dept_no, de.emp_no,max(salary) from
dept_emp as de left join salaries as s on de.emp_no = s.emp_no
group by dept_no order by dept_no asc
错误点1:d.emp_no是非聚合字段,不能出现在SELECT。因为一个聚合字段(dept_no)对应多个非聚合字段(emp_no),所以选择的时候,会随机选择非聚合字段中的任何一个,于是出错。
错误点2:根据错误点1,如果强行写上述代码,当多人同时拥有最高薪水时也无法查出。
使用开窗函数修改SQL
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
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;