题解 | #获取每个部门中当前员工薪水最高的相关信息#

获取每个部门中当前员工薪水最高的相关信息

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中执行报错;

全部评论

推荐最新楼层

大堰河，我的保姆

农民动运讲习所数据库工程师

可以啊

点赞回复分享

发布于 2022-09-24 17:22 上海

昨天 16:37

云南大学 Java

趣链科技Java 26/27实习生避雷

一面常规八股，几乎全部答出来二面简单描述项目之后聊天，聊了入职情况等，以为稳了，两天直接感谢信，KPI建议避雷背景学院本，垫底211人智硕

BerC：这家公司贼恶心，面试官一脸蔑视，本来想着拿来面试练手，还被嘲讽

，就是坨屎

点赞评论收藏

05-12 13:14

已编辑

中山大学算法工程师

这种简历还能找到实习吗...

有气魄的海豚在喝茶：第一次在牛客看到三九博

你的简历改到第几版了简历中的项目经历要怎么写

点赞评论收藏

04-30 21:35

已编辑

长安大学 C++

美团小sp。。可惜了😪😪————更新——————别说我了兄弟们😭😭。女朋友是体制内高校教师，工作动不了。并且对我真的是非常非常好。在一块也是他出钱居多。并且双方也都见过父母，准备订婚了😭😭😭😭

晓沐咕咕咕：评论区没被女朋友好好对待过的计小将可真多。觉得可惜可以理解，毕竟一线大厂sp。但是骂楼主糊涂的大可不必，说什么会被社会毒打更是丢人。女朋友体制内生活有保障，读研女朋友还供着，都准备订婚了人家两情相悦，二线本地以后两口子日子美滋滋，哪轮到你一个一线城市房子都买不起的996清高计小将在这说人家傻😅

点赞评论收藏