The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
The Department table holds all departments of the company.
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows (order of rows does not matter).
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
Explanation:
In IT department, Max earns the highest salary, both Randy and Joe earn the second highest salary, and Will earns the third highest salary. There are only two employees in the Sales department, Henry earns the highest salary while Sam earns the second highest salary.此题的难度在于，选择部门的前三位高工资人员（注意，允许并列人员的存在）。
分析题目：
- 存在两张表，则肯定需要使用
join； - 需要选取相同部门的前三名，原本想使用
group by以及limit； - 然而
group by以及limit无法满足并列前三名的要求，因此，只能对同张表使用select count，如果某个薪水满足超过其的薪水（注意是不同的薪水）小于三个，则此人薪水在部门前三；
综上所述，答案如下所示：
# Write your MySQL query statement below
SELECT Employee1.Name AS Employee, Employee1.Salary, Department.Name AS Department
FROM Employee AS Employee1, Department
WHERE
Employee1.DepartmentId = Department.Id
AND 3 > (
SELECT COUNT(DISTINCT Employee2.Salary)
FROM Employee AS Employee2
WHERE
Employee1.DepartmentId = Employee2.DepartmentId
AND Employee1.Salary < Employee2.Salary
)内容来源于网络如有侵权请私信删除
- 还没有人评论，欢迎说说您的想法！
客服
