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

该题目是一个耳熟能详的题目了，在网上也有许多解答方案。这里我只讲一种就是使用in的元组方式查询出数据。

①创建两张表

创建部门员工表dept_emp

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept_emp
-- ----------------------------
DROP TABLE IF EXISTS `dept_emp`;
CREATE TABLE `dept_emp`  (
  `emp_no` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工号',
  `dept_no` int(11) NOT NULL COMMENT '部门id',
  `emp_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
  `emp_age` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '员工年龄',
  PRIMARY KEY (`emp_no`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept_emp
-- ----------------------------
INSERT INTO `dept_emp` VALUES (1, 1, '雍正', '25');
INSERT INTO `dept_emp` VALUES (2, 2, '康熙', '30');
INSERT INTO `dept_emp` VALUES (3, 1, '顺治', '36');
INSERT INTO `dept_emp` VALUES (4, 2, '索尔图', '45');
INSERT INTO `dept_emp` VALUES (5, 3, '明珠', '16');
INSERT INTO `dept_emp` VALUES (6, 3, '武则天', '80');
INSERT INTO `dept_emp` VALUES (7, 1, '张居正', '35');
INSERT INTO `dept_emp` VALUES (8, 2, '王阳明', '13');
INSERT INTO `dept_emp` VALUES (9, 1, '多尔衮', '26');

SET FOREIGN_KEY_CHECKS = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

创建薪水表salaries

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for salaries
-- ----------------------------
DROP TABLE IF EXISTS `salaries`;
CREATE TABLE `salaries`  (
  `emp_no` int(11) NOT NULL AUTO_INCREMENT COMMENT '薪水主键',
  `salary` decimal(10, 2) NULL DEFAULT NULL COMMENT '薪水',
  PRIMARY KEY (`emp_no`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of salaries
-- ----------------------------
INSERT INTO `salaries` VALUES (1, 50.00);
INSERT INTO `salaries` VALUES (2, 100.00);
INSERT INTO `salaries` VALUES (3, 20.00);
INSERT INTO `salaries` VALUES (4, 30.00);
INSERT INTO `salaries` VALUES (5, 60.00);
INSERT INTO `salaries` VALUES (6, 80.00);
INSERT INTO `salaries` VALUES (7, 10.00);
INSERT INTO `salaries` VALUES (8, 5.00);
INSERT INTO `salaries` VALUES (9, 160.00);

SET FOREIGN_KEY_CHECKS = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

②讲解group by 特性

group by 分组后能查询分组字段以及使用聚合函数的字段，对于其他未使用到函数的字段是不能够进行查询的，mysql能执行该查询语句，但是如果不满足上述条件，查询出来的数据是一个随机的，而oracle无法执行查询。

SELECT dept_no,max(s.salary) from dept_emp d,salaries s where d.emp_no = s.emp_no group by d.dept_no 
1

上面这条语句为了查询部门员工表部门号，以及其对应的最高薪水，因为是根据部门进行分组的，所以可以查询出部门这个字段，并且可以查询出聚合函数带有薪水字段的，查询结果如下图：

如果只是查询每个部门以及对应的最高薪水的信息，这个查询语句足够了，但是要想查询出这个最高薪水所对应的员工号，还是不够。

③ in 的用法
in可以使用元组形式，所谓元组形式，就是字段属性可以多个作为查询条件，进行查询。

SELECT  de.*,sa.salary  from  dept_emp de,salaries sa where de.emp_no = sa.emp_no and (de.dept_no,sa.salary) in
(
SELECT dept_no,max(s.salary) from dept_emp d,salaries s where d.emp_no = s.emp_no group by d.dept_no 
)
1
2
3
4

上面这个语句括号里边的语句上面已经讲过了，就是一个分组查询，而外面的语句就是一个in查询，只是这个in查询是两个属性在一块作为条件进行查询，输出结果如下图：

这就是该题目的答案。

④验证

SELECT  de.*,sa.salary  from  dept_emp de,salaries sa where de.emp_no = sa.emp_no and de.dept_no = 1 
SELECT  de.*,sa.salary  from  dept_emp de,salaries sa where de.emp_no = sa.emp_no and de.dept_no = 2 
SELECT  de.*,sa.salary  from  dept_emp de,salaries sa where de.emp_no = sa.emp_no and de.dept_no = 3
1
2
3

分别执行上述三个语句，执行结果如下：



很清晰明了了吧，充分验证了上面的语句是没有问题的。