题目
获取所有部门中当前(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');我的解答
select d.dept_no,d.emp_no,s.salary from dept_emp d left join salaries s on d.emp_no=s.emp_no where d.to_date='9999-01-01' and s.to_date='9999-01-01' order by d.dept_no asc limit 1错误:通过率50%
题解
错误示范 ## 错误示范(虽然能通过测试，但实际上是错的) 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)实际上是想筛选分组中的记录。这是错误的。 补充 group by 与 having的使用 ## 补充：GROUP BY语句与HAVING语句的使用：参考资料：https://www.cnblogs.com/geogre123/p/11177204.html ## 语法： 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; 尝试 select d1.dept_no,d1.emp_no,s1.salary from dept_emp d1 inner join salaries 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 d2 inner join salaries s2 on d2.emp_no=s2.emp_no and d2.to_date='9999-01-01' and s2.to_date='9999-01-01' and d1.dept_no=d2.dept_no ) order by d1.dept_no或者另一种写法
SELECT d.dept_no, d.emp_no, MAX(s.salary) -- 其实不能写d.emp_no，它既不是分组字段，也不是聚合操作 FROM dept_emp AS d INNER JOIN salaries AS s ON d.emp_no=s.emp_no WHERE d.to_date="9999-01-01" GROUP BY d.dept_no 分析两个表
关联子查询
外表固定一个部门，查询顺序是外表部门号d1.dept_no传入内部的d2.dept_no
内部子查询查出来最大的薪水
外表筛选出来to_date='9999-01-01’的员工和所属部门
内表通过 外表（第一条记录）传入的部门号到内表查询出来最大的salary，并根据外表此条记录的salay判断是否in 最大的内表salary
注意的是，相关子查询，子查询的条件依赖于父查询，外层的属性值传入内层，外层获得判断数据后，外层·在根据内层的数据筛选
