# 185. Department Top Three Salaries(SQL)

{% hint style="info" %}
将两个表格合并成需要的样子，然后选每个部门工资最高的三个。

`where` 语句意思是同部门比自己工资高的不超过三个，那就是选出来了最高的三个。
{% endhint %}

{% tabs %}
{% tab title="写法一" %}

```sql
# Write your MySQL query statement below
select d.Name Department, e.Name Employee, e.Salary Salary
from Employee e 
    join Department d on e.DepartmentId = d.Id
where 
    3 > (select count(distinct e2.Salary)
         from Employee e2
         where e2.Salary > e.Salary 
             and e.DepartmentId = e2.DepartmentId);
```

{% endtab %}

{% tab title="写法二" %}

```sql
SELECT d.Name AS Department, e.Name AS Employee, e.Salary 
FROM Employee e
    JOIN Department d on e.DepartmentId = d.Id
WHERE (SELECT COUNT(DISTINCT Salary) 
        FROM Employee 
        WHERE Salary > e.Salary AND DepartmentId = d.Id) 
        < 3 ORDER BY d.Name, e.Salary DESC;
```

{% endtab %}
{% endtabs %}

{% hint style="danger" %}
仍然不明白`where 3 > count(Salary)` 语句如何生效，filter前面合并的表格。
{% endhint %}