Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询,找出每个部门工资前三高的员工。例如,根据上述给定的表格,查询结果应返回:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
1、链接两表,将部门id换成name
2、如何取前三高?不妨再加入一张Employee表,将其与1中的表作对比,令e2表中的salary大于e1表中的salary
3、限制条件:e2表中salary大于e1表中salary的个数少于3 ==》取前三高的salary
So show you my code:
# Write your MySQL query statement below
SELECT Department.Name AS Department, e1.Name AS Employee, e1.Salary AS Salary
FROM Employee e1
JOIN Department
ON e1.DepartmentId = Department.Id
WHERE 3 > (
SELECT COUNT(DISTINCT e2.Salary)
FROM Employee e2
WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId
)
ORDER BY Department.Name, e1.Salary DESC
Runtime: 746 ms
注意:条件语句中必须为 3 > (...),子查询中必须是 e2.Salary>e1.Salary
为什么呢?
不妨假设e1=e2=[6,5,4,3],则子查询的过程如下:
1、e1.Salary=3;则e2.Salary可以取4、5、6;COUNT(DISTINCT e2.Salary)=3(比工资为3块钱高的人有3个。加上他自己,则列出的是部门前四高工资的人)
2、e1.Salary=4;则e2.Salary可以取5、6;COUNT(DISTINCT e2.Salary)=2(比工资为4块钱高的人有2个。加上他自己,则列出的是部门前三高工资的人)
3、e1.Salary=5;则e2.Salary可以取6;COUNT(DISTINCT e2.Salary)=1(比工资为5块钱高的人有1个。加上他自己,则列出的是部门前二高工资的人)
4、e1.Salary=6;则e2.Salary无法取值;COUNT(DISTINCT e2.Salary)=0(比工资为6块钱高的人有0个。加上他自己,则列出的是部门前一高(最高)工资的人)
则要令COUNT(DISTINCT e2.Salary) < 3 的情况才符合题目要求“取得前三高”,所以有上述的4、3、2.
也即是说,这等价于取e1.Salary最大的三个值。
————————————————
原文链接,本文有增加补充说明:https://blog.csdn.net/qq_41822173/article/details/80978544