LeetCode——Department Top Three Salaries(巧妙使用子查询)

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
    )