185. Department Top Three Salaries(SQL)

# Hard

将两个表格合并成需要的样子，然后选每个部门工资最高的三个。

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

写法一

# 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);

写法二

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;

仍然不明白where 3 > count(Salary) 语句如何生效，filter前面合并的表格。