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
)
PS:
如果您覺得我的文章對您有幫助，請關注我的微信公眾號，謝謝!
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/121224.html
標籤：MySQL