题解 | #获取每个部门中当前员工薪水最高的相关信息#
获取每个部门中当前员工薪水最高的相关信息
http://www.nowcoder.com/practice/4a052e3e1df5435880d4353eb18a91c6
题解
SELECT t_dep_maxsal.dep_no,s.emp_no,t_dep_maxsal.dep_maxsal FROM(
SELECT dep_no,MAX(sal) dep_maxsal FROM (
SELECT de.dept_no dep_no,de.emp_no emp_no,salary sal
FROM dept_emp de
JOIN salaries s
ON de.emp_no=s.emp_no
) t_dep_emp_sal
GROUP BY t_dep_emp_sal.dep_no
)t_dep_maxsal
LEFT JOIN salaries s
ON t_dep_maxsal.dep_maxsal=s.salary
AND s.emp_no IN( SELECT emp_no FROM dept_emp WHERE dept_no=t_dep_maxsal.dep_no)
ORDER BY dep_no ASC;测试数据与题解详细说明
测试数据：
#测试数据1 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,'d002','1996-08-09','9999-01-01'); INSERT INTO dept_emp VALUES(10004,'d003','1996-08-08','9999-01-01'); INSERT INTO dept_emp VALUES(10005,'d003','1996-08-07','9999-01-01'); INSERT INTO dept_emp VALUES(10006,'d003','1996-08-07','9999-01-01'); INSERT INTO salaries VALUES(10001,78958,'2002-06-22','9999-01-01'); INSERT INTO salaries VALUES(10002,82527,'2001-08-02','9999-01-01'); INSERT INTO salaries VALUES(10003,92527,'2001-08-07','9999-01-01'); INSERT INTO salaries VALUES(10004,82527,'2001-08-08','9999-01-01'); INSERT INTO salaries VALUES(10005,53527,'2001-08-06','9999-01-01'); INSERT INTO salaries VALUES(10006,93527,'2001-08-06','9999-01-01'); #测试数据1-表数据 SELECT * FROM dept_emp; +--------+---------+------------+------------+ | emp_no | dept_no | from_date | to_date | +--------+---------+------------+------------+ | 10001 | d001 | 1986-06-26 | 9999-01-01 | | 10002 | d001 | 1996-08-03 | 9999-01-01 | | 10003 | d002 | 1996-08-09 | 9999-01-01 | | 10004 | d003 | 1996-08-08 | 9999-01-01 | | 10005 | d003 | 1996-08-07 | 9999-01-01 | | 10006 | d003 | 1996-08-07 | 9999-01-01 | +--------+---------+------------+------------+ 6 ROWS IN SET (0.00 sec) SELECT * FROM salaries; +--------+--------+------------+------------+ | emp_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 10001 | 78958 | 2002-06-22 | 9999-01-01 | | 10002 | 82527 | 2001-08-02 | 9999-01-01 | | 10003 | 92527 | 2001-08-07 | 9999-01-01 | | 10004 | 82527 | 2001-08-08 | 9999-01-01 | | 10005 | 53527 | 2001-08-06 | 9999-01-01 | | 10006 | 93527 | 2001-08-06 | 9999-01-01 | +--------+--------+------------+------------+ 6 ROWS IN SET (0.00 sec) #测试数据2 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,'d002','1996-08-09','9999-01-01'); INSERT INTO dept_emp VALUES(10004,'d003','1996-08-08','9999-01-01'); INSERT INTO dept_emp VALUES(10005,'d003','1996-08-07','9999-01-01'); INSERT INTO dept_emp VALUES(10006,'d003','1996-08-07','9999-01-01'); INSERT INTO dept_emp VALUES(10007,'d002','1996-08-07','9999-01-01'); INSERT INTO dept_emp VALUES(10008,'d002','1996-08-07','9999-01-01'); INSERT INTO salaries VALUES(10001,78958,'2002-06-22','9999-01-01'); INSERT INTO salaries VALUES(10002,82527,'2001-08-02','9999-01-01'); INSERT INTO salaries VALUES(10003,92527,'2001-08-07','9999-01-01'); INSERT INTO salaries VALUES(10004,82527,'2001-08-08','9999-01-01'); INSERT INTO salaries VALUES(10005,53527,'2001-08-06','9999-01-01'); INSERT INTO salaries VALUES(10006,93527,'2001-08-06','9999-01-01'); INSERT INTO salaries VALUES(10007,94527,'2001-08-06','9999-01-01'); INSERT INTO salaries VALUES(10008,94527,'2001-08-06','9999-01-01'); #测试数据2-表数据 SELECT * FROM dept_emp; +--------+---------+------------+------------+ | emp_no | dept_no | from_date | to_date | +--------+---------+------------+------------+ | 10001 | d001 | 1986-06-26 | 9999-01-01 | | 10002 | d001 | 1996-08-03 | 9999-01-01 | | 10003 | d002 | 1996-08-09 | 9999-01-01 | | 10004 | d003 | 1996-08-08 | 9999-01-01 | | 10005 | d003 | 1996-08-07 | 9999-01-01 | | 10006 | d003 | 1996-08-07 | 9999-01-01 | | 10007 | d002 | 1996-08-07 | 9999-01-01 | | 10008 | d002 | 1996-08-07 | 9999-01-01 | +--------+---------+------------+------------+ 8 ROWS IN SET (0.00 sec) SELECT * FROM salaries; +--------+--------+------------+------------+ | emp_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 10001 | 78958 | 2002-06-22 | 9999-01-01 | | 10002 | 82527 | 2001-08-02 | 9999-01-01 | | 10003 | 92527 | 2001-08-07 | 9999-01-01 | | 10004 | 82527 | 2001-08-08 | 9999-01-01 | | 10005 | 53527 | 2001-08-06 | 9999-01-01 | | 10006 | 93527 | 2001-08-06 | 9999-01-01 | | 10007 | 94527 | 2001-08-06 | 9999-01-01 | | 10008 | 94527 | 2001-08-06 | 9999-01-01 | +--------+--------+------------+------------+ 8 ROWS IN SET (0.00 sec)
题解详细说明：
★★★使用测试数据1
1.1、连接查询-得到部门id与员工id及其工资
SELECT de.dept_no,de.emp_no,salary
FROM dept_emp de
JOIN salaries s
ON de.emp_no=s.emp_no;
+---------+--------+--------+
| dept_no | emp_no | salary |
+---------+--------+--------+
| d001 | 10001 | 78958 |
| d001 | 10002 | 82527 |
| d002 | 10003 | 92527 |
| d003 | 10004 | 82527 |
| d003 | 10005 | 53527 |
| d003 | 10006 | 93527 |
+---------+--------+--------+
6 ROWS IN SET (0.00 sec)
1.2、子查询+分组查询-将1.1查询结果作为临时表（from子查询），以部门id分组，得到各部门最高工资
SELECT dep_no,MAX(sal) FROM (
SELECT de.dept_no dep_no,de.emp_no emp_no,salary sal
FROM dept_emp de
JOIN salaries s
ON de.emp_no=s.emp_no
) t_dep_emp_sal
GROUP BY t_dep_emp_sal.dep_no;
+--------+----------+
| dep_no | MAX(sal) |
+--------+----------+
| d001 | 82527 |
| d002 | 92527 |
| d003 | 93527 |
+--------+----------+
3 ROWS IN SET (0.00 sec)
1.3、子查询+连接查询-把1.2查询结果作为临时表（from子查询）,连接薪资表，得到与最高薪资相同的员工id（这时部门id与员工id有不匹配的数据）
SELECT t_dep_maxsal.dep_no,s.emp_no,t_dep_maxsal.dep_maxsal FROM(
SELECT dep_no,MAX(sal) dep_maxsal FROM (
SELECT de.dept_no dep_no,de.emp_no emp_no,salary sal
FROM dept_emp de
JOIN salaries s
ON de.emp_no=s.emp_no
) t_dep_emp_sal
GROUP BY t_dep_emp_sal.dep_no
)t_dep_maxsal
LEFT JOIN salaries s
ON t_dep_maxsal.dep_maxsal=s.salary;
+--------+--------+------------+
| dep_no | emp_no | dep_maxsal |
+--------+--------+------------+
| d001 | 10002 | 82527 |
| d001 | 10004 | 82527 |
| d002 | 10003 | 92527 |
| d003 | 10006 | 93527 |
+--------+--------+------------+
4 ROWS IN SET (0.00 sec)
1.4、在1.3的基础上继续筛选; 条件：员工需是该部门的员工 最后再按部门id升序排序
SELECT t_dep_maxsal.dep_no,s.emp_no,t_dep_maxsal.dep_maxsal FROM(
SELECT dep_no,MAX(sal) dep_maxsal FROM (
SELECT de.dept_no dep_no,de.emp_no emp_no,salary sal
FROM dept_emp de
JOIN salaries s
ON de.emp_no=s.emp_no
) t_dep_emp_sal
GROUP BY t_dep_emp_sal.dep_no
)t_dep_maxsal
LEFT JOIN salaries s
ON t_dep_maxsal.dep_maxsal=s.salary
AND s.emp_no IN( SELECT emp_no FROM dept_emp WHERE dept_no=t_dep_maxsal.dep_no)
ORDER BY dep_no ASC;
+--------+--------+------------+
| dep_no | emp_no | dep_maxsal |
+--------+--------+------------+
| d001 | 10002 | 82527 |
| d002 | 10003 | 92527 |
| d003 | 10006 | 93527 |
+--------+--------+------------+
3 ROWS IN SET (0.00 sec)
★★★使用测试数据2
数据说明：同一个部门有两个员工薪资相同且是部门最高薪资
SELECT t_dep_maxsal.dep_no,s.emp_no,t_dep_maxsal.dep_maxsal FROM(
SELECT dep_no,MAX(sal) dep_maxsal FROM (
SELECT de.dept_no dep_no,de.emp_no emp_no,salary sal
FROM dept_emp de
JOIN salaries s
ON de.emp_no=s.emp_no
) t_dep_emp_sal
GROUP BY t_dep_emp_sal.dep_no
)t_dep_maxsal
LEFT JOIN salaries s
ON t_dep_maxsal.dep_maxsal=s.salary
AND s.emp_no IN( SELECT emp_no FROM dept_emp WHERE dept_no=t_dep_maxsal.dep_no)
ORDER BY dep_no ASC;
+--------+--------+------------+
| dep_no | emp_no | dep_maxsal |
+--------+--------+------------+
| d001 | 10002 | 82527 |
| d002 | 10007 | 94527 |
| d002 | 10008 | 94527 |
| d003 | 10006 | 93527 |
+--------+--------+------------+
4 rows in set (0.00 sec)
题目要求是获取每个部门中当前员工薪水最高的相关信息，所以要把部门2这两个员工信息都查询出来，符合题意。关于分组查询（分组函数）的一些问题
- 分组函数在使用的时候必须先进行分组，然后才能用，如果没有对数据进行分组，整张表默认为一组。
- 和分组函数一同查询的字段要求是group by后的字段（在一条select语句当中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数，其它的一律不能跟）
SELECT AVG(salary),employee_id FROM employees GROUP BY employee_id; 可执行SELECT AVG(salary),employee_id,job_id FROM employees GROUP BY employee_id;该语句mysql中可以执行，但无意义;oracle中执行报错;
投递快看等公司6个岗位