部门工资前三高的所有员工

最新推荐文章于 2025-05-03 02:00:00 发布

原创最新推荐文章于 2025-05-03 02:00:00 发布 · 232 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

这篇博客探讨了一个SQL查询问题，涉及从`employee`和`department`表中找出每个部门薪资排名前三的员工。当部门人数不足三人时，查询需包含所有员工。解决方案是通过子查询获取部门第三高薪资，并设置若无第三高则为0，然后筛选出薪资大于等于此值的员工。

前言

力扣上发现的mysql查询题，题目链接

表结构sql 和基本数据

DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES (1, 'IT');
INSERT INTO `department` VALUES (2, 'Sales');

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `salary` decimal(10, 2) NULL DEFAULT NULL,
  `departmentId` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1, 'Joe', 85000.00, 1);
INSERT INTO `employee` VALUES (2, 'Henry', 80000.00, 2);
INSERT INTO `employee` VALUES (3, 'Sam', 60000.00, 2);
INSERT INTO `employee` VALUES (4, 'Max', 90000.00, 1);
INSERT INTO `employee` VALUES (5, 'Janet', 69000.00, 1);
INSERT INTO `employee` VALUES (6, 'Randy', 85000.00, 1);
INSERT INTO `employee` VALUES (7, 'Will', 70000.00, 1);

SET FOREIGN_KEY_CHECKS = 1;

思路

本来是想用limit直接查出每个部门薪资前3的就行了，结果题目要求薪资相等的并列，这样就行不通了。
想了下可以筛选查出部门第三高的薪资，然后再查出大于等于这个薪资的不就完了。
然后发现又埋了个坑，如果一个部门小于三个人那不就没有第三高（查出薪资结果为null）的吗。想了好久，同然灵机一动加个ifnull=0不就行了吗，小于三人，所有人都查出来，薪资大于0，不就所有都出来了吗。

查询sql

SELECT
	a.NAME AS Department,
	e.NAME AS Employee,
	e.salary AS Salary 
FROM
	employee e
	LEFT JOIN (
	SELECT
		id,
		ifnull(/*这里是查询出部门第三高的薪资，如果没有第三高，说明该部门小于三人，把薪资门槛设置成0就行了*/
			( SELECT salary FROM employee WHERE departmentid = d.id GROUP BY departmentid, salary ORDER BY departmentid, salary DESC LIMIT 2, 1 ),
			0 
		) AS min,
		d.NAME 
	FROM
		department d 
	) a ON a.id = e.departmentId 
WHERE
	e.salary >= a.min 
ORDER BY
	e.departmentid,
	e.salary DESC;