MySQL 查询部门工资前三高的员工信息

1. 要求：编写一个SQL，获取部门工资前三高的员工信息
2. 员工表和部门表结构：

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255),
  `salary` decimal(10,2),
  `department_id` int(11),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

3. 员工表和部门表数据：

INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (1, 'Joe', 70000.00, 1);
INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (2, 'Henry', 80000.00, 2);
INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (3, 'Sam', 60000.00, 2);
INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (4, 'Max', 90000.00, 1);
INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (5, 'Janet', 69000.00, 1);
INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (6, 'Randy', 85000.00, 1);
INSERT INTO `employee`(`id`, `name`, `salary`, `department_id`) VALUES (7, 'Eva', 85000.00, 1);

INSERT INTO `department`(`id`, `name`) VALUES (1, 'IT');
INSERT INTO `department`(`id`, `name`) VALUES (2, 'Sales');

4.答案：

SELECT
	d.`name` AS '部门',
	e.`name` AS '员工',
	e.salary AS '工资' 
FROM
	employee e
	INNER JOIN department d ON d.id = e.department_id 
WHERE
	(
	SELECT count(DISTINCT em.salary) FROM employee em WHERE em.salary > e.salary AND em.department_id = e.department_id
	) < 3 
ORDER BY e.department_id, e.salary DESC

结果：

分析：

对于下面的语句，我们不妨把它去除，再执行上面的 sql，看看结果
WHERE
	(
	SELECT count(DISTINCT em.salary) FROM employee em WHERE em.salary > e.salary AND em.department_id = e.department_id
	) < 3 

SELECT
	d.`name` AS '部门',
	e.`name` AS '员工',
	e.salary AS '工资' 
FROM
	employee e
	INNER JOIN department d ON d.id = e.department_id 

ORDER BY e.department_id, e.salary DESC

结果：

可以看到比正确答案数据多出一条
接着分析去除的 sql 语句

DISTINCT 字段名：过滤字段中的重复记录

子查询中 em.salary > e.salary：
说明：
当 em = e = [90000,85000,70000，69000]
当 e.salary = 69000 时，则 em.salary 可以取值 [90000,85000,70000]，则 count(DISTINCT em.salary) = 3
当 e.salary = 70000 时，则 em.salary 可以取值 [90000,85000]，则 count(DISTINCT em.salary) = 2
当 e.salary = 85000 时，则 em.salary 可以取值 [90000]，则 count(DISTINCT em.salary) = 1
当 e.salary = 90000 时，则 em.salary 可以取值 []，则 count(DISTINCT em.salary) = 0

最后要让 count(DISTINCT em.salary) < 3，则 e.salary 可以取值为 [70000,85000,90000] 即集合前 3 高的工资
由此可知，对于这道题目，这里是理解的重点

通过结果可以看到，第二名员工和第三名员工工资相同，被当作并列第二，并不会排挤掉第三名。如果我们希望出现并列第二名时，第三名就变成第四名呢？可以把count(DISTINCT em.salary) 改成 count(*)

SELECT
	d.`name` AS '部门',
	e.`name` AS '员工',
	e.salary AS '工资' 
FROM
	employee e
	INNER JOIN department d ON d.id = e.department_id 
WHERE
	( SELECT count( * ) FROM employee em WHERE em.salary > e.salary AND em.department_id = e.department_id ) < 3 
ORDER BY
	e.department_id,
	e.salary DESC

结果：

参考1：https://blog.csdn.net/qq_20009015/article/details/103338237
参考2：https://blog.csdn.net/a295567172/article/details/87779962