SQL实战12.获取所有部门中当前员工薪水最高的相关信息

题目

解答

自写代码：

select d.dept_no, d.emp_no, s.salary
from dept_emp as d
inner join salaries as s
on d.emp_no=s.emp_no
where d.to_date='9999-01-01’
and s.to_date='9999-01-01'
group by d.dept_no
having salary=max(s.salary);

错误点1：d.emp_no是非聚合字段，不能出现在SELECT。因为一个聚合字段(dept_no)对应多个非聚合字段(emp_no)，所以选择的时候，会随机选择非聚合字段中的任何一个，于是出错。
错误点2：根据错误点1，如果强行写上述代码，当多人同时拥有最高薪水时也无法查出。
错误点3：HAVING语句用来与聚合函数联合使用，过滤GROUP BY语句返回的记录集，筛选的是分组，例如HAVING AVG(score)>=80，筛选的是那些平均成绩大于80的分组，而上面的HAVING salary=MAX(s.salary)实际上是想筛选分组中的记录。这是错误的。
正确答案：关联子查询，外表固定一个部门，内表进行子查询

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;

补充：如果这题不需要给出emp_no(即只求所有部门中当前员工薪水最高值)，则用INNER JOIN和GROUP BY和MAX即可解决：

SELECT d.dept_no, MAX(s.salary)
FROM dept_emp as d
INNER JOIN salaries as s
ON d.emp_no=s.emp_no
AND d.to_date='9999-01-01'
AND s.to_date='9999-01-01'
GROUP BY d.dept_no;

二刷
方法一：

with tmp as (
select d.emp_no,d.dept_no,s.salary
from dept_emp as d
left join salaries as s
on d.emp_no = s.emp_no
)

select dept_no,emp_no,salary as maxSalary
from tmp 
where (dept_no,salary) in ( select dept_no,max(salary)
from tmp
group by dept_no)
order by dept_no;

方法二：

with tmp as (
select d.emp_no,d.dept_no,s.salary,row_number() over (partition by d.dept_no order by s.salary desc) as ranking
from dept_emp as d
left join salaries as s
on d.emp_no = s.emp_no
)

select dept_no,emp_no,salary as maxSalary
from tmp 
where ranking = 1
order by dept_no;