牛客网SQL12:获取所有部门中当前员工薪水最高的相关信息
解析:初看本题,不假思索地写下如下答案:
SELECT dept_no, s.emp_no, MAX(salary) AS maxSalary
FROM salaries s, dept_emp de
WHERE de.emp_no = s.emp_no
GROUP BY dept_no
ORDER BY dept_no;
但是由于salaries.emp_no是非聚合列,放在SELECT中会默认返回该列的第一个记录,导致emp_no与最大salary不匹配出现错误;
于是考虑先选定最大salary,再根据最大salary选定对应的dept_no和emp_no,于是又写出如下代码:
SELECT dept_no, salaries.emp_no, salary
FROM salaries
INNER JOIN dept_emp ON dept_emp.emp_no = salaries.emp_no
WHERE salary IN (SELECT MAX(salary) AS maxSalary
FROM salaries s, dept_emp de
WHERE de.emp_no = s.emp_no
GROUP BY dept_no)
ORDER BY dept_no;
原以为这样可以通过了,但是又出现了新的问题:由于我们是通过dept_emp.emp_no = salaries.emp_no将两张表联结在一起的,但是当数据中出现A部门的最大薪水恰好等于B部分的非最大薪水时,会导致将这两个全部列出来,从而出现错误。
于是想到,既然问题是仅通过maxSalary无法让emp_no的值完全正确匹配,那就使用maxSalary和dept_no同时去匹配,然后我发现,在IN操作符中使用子查询时,返回多个列也能对应匹配,于是我是先通过子查询语句获得最大salary和对应的dept_no,据此可匹配出唯一的emp_no,代码如下:
SELECT dept_no, salaries.emp_no, salary AS maxSalary
FROM salaries
INNER JOIN dept_emp ON dept_emp.emp_no = salaries.emp_no
WHERE (dept_no, salary) IN (SELECT dept_no, MAX(salary) AS maxSalary
FROM salaries s, dept_emp de
WHERE de.emp_no = s.emp_no
GROUP BY dept_no)
-- and dept_emp.to_date='9999-01-01'
-- and salaries.to_date='9999-01-01'
ORDER BY dept_no;
上面我注释掉了to_date=‘9999-01-01’,这个是限制员工是否在职的条件,如果加上可以使已经离职的员工不参与比较。