SQL12获取所有部门中当前员工薪水最高的相关信息

题目描述

获取所有部门中当前(dept_emp.to_date = '9999-01-01')员工当前(salaries.to_date='9999-01-01')薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门升序排列。 CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`)); CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`from_date`));

如插入：

INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01'); INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01'); INSERT INTO dept_emp VALUES(10003,'d001','1996-08-03','1997-08-03'); INSERT INTO salaries VALUES(10001,90000,'1986-06-26','1987-06-26'); INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01'); INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03'); INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02'); INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01'); INSERT INTO salaries VALUES(10003,90000,'1996-08-03','1997-08-03');  

则输出

dept_noemp_nosalaryd0011000188958

错误答案 

SELECT DE.dept_no,DE.emp_no,S.salary FROM dept_emp DE INNER JOIN slaries S ON DE.emp_no=S.emp_no WHERE DE.to_date = S.to_date AND S.to_date='9999-01-01' GROUP BY DE.dept_no HAVING S.salary=MAX(S.salary) ORDER BY DE.dept_no ASC;

分析

错误点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)实际上是想筛选分组中的记录。这是错误的。

补充： GROUP BY语句与HAVING语句的使用：参考资料：https://blog.csdn.net/W_H_M_2018/article/details/109197994 语法：

SELECT column_1, column_2, … column_n, aggregate_function(expression), constant FROM tables WHERE predicates GROUP BY column_1, column_2, … column_n HAVING condition_1 … condition_n;

注意：因为聚合函数通过作用一组值而只返回一个单一值，因此，在SELECT语句中出现的字段要么为一个聚合函数的输入值，如COUNT(course)，要么为GROUP BY语句中指定的字段，要么是常数，否则会出错。 简而言之：使用GROUP BY子句时，SELECT子句中只能有聚合键、聚合函数、常数。

正确答案 

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;