题解-牛客网-SQL-(SQL12)获取所有部门中当前员工薪水最高的相关信息

1. 题目描述

1.1 输入

有一个员工表dept_emp简况如下:

dept_emp表的输入数据如下：

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,'d004','1995-12-03','9999-01-01');
INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');

有一个薪水表salaries简况如下:

salaries表的输入数据如下：

INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
INSERT INTO salaries VALUES(10009,95409,'2002-02-14','9999-01-01');
INSERT INTO salaries VALUES(10010,94409,'2001-11-23','9999-01-01');

1.2 输出

获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列，以上例子输出如下:

2. 题目分析及解答

2.1 题目分析

查询要求按照部门分别获取最高薪水的员工的相关信息，很容易想到GROUP BY子句，从而写出如下SQL查询语句：

SELECT
	d.dept_no,
	d.emp_no,
	MAX( s.salary ) AS maxSalary 
FROM
	dept_emp AS d
	JOIN salaries AS s ON d.emp_no = s.emp_no 
GROUP BY
	d.dept_no;

该语句对GROUP BY子句的使用是不恰当的。首先回顾一下GROUP BY子句的特点：

• 使用GROUP BY子句时，SELECT子句中只能含有聚合键（即GROUP BY后面的字段名）、聚合函数和常数；
• GROUP BY子句默认取非聚合的第一条记录。

在该语句中，d.emp_no为SELECT子句中的非聚合键，由于GROUP BY子句默认取非聚合的第一条记录，从而可能导致如下不良后果：

• 查询结果返回的员工编号emp_no不一定对应于当前部门的最高薪水，因为；
• 如果有多个员工都拿到了当前部门的最高薪水，查询结果中也只显示其中一位，导致结果显示不完整。

实际上，该题旨在考查关联子查询的使用。在该题中，父表固定为员工信息，子表进行子查询，返回每个部门的最高薪水。

2.2 详细解答

根据题目分析可直接写出相应的SQL查询语句：

SELECT
	d1.dept_no,
	s1.emp_no,
	s1.salary AS maxSalary 
FROM
	salaries AS s1
	JOIN dept_emp AS d1 ON d1.emp_no = s1.emp_no 
WHERE
	s1.salary IN (
	SELECT
		MAX( s2.salary ) 
	FROM
		dept_emp AS d2
		JOIN salaries AS s2 ON d2.emp_no = s2.emp_no 
	WHERE
		d1.dept_no = d2.dept_no  -- 子表和父表结果相关联
	GROUP BY
		d2.dept_no
	) 
ORDER BY
	d1.dept_no;