此題的難度在於，選擇部門的前三位高工資人員（註意，允許併列人員的存在）。 分析題目： 存在兩張表，則肯定需要使用 ； 需要選取相同部門的前三名，原本想使用 以及 ； 然而 以及 無法滿足併列前三名的要求，因此，只能對同張表使用 ，如果某個薪水滿足 小於三個，則此人薪水在部門前三； 綜上所述，答案如下 ...
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
)
