LeetCode 185. 部门工资前三高的所有员工

https://leetcode-cn.com/problems/department-top-three-salaries/

 这个是在where里求排名来筛选。

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

 

这是一种先求出排名，结果作为一个表，然后再连接。代码比较繁琐

# Write your MySQL query statement below
select 
    D.Name as 'Department',
    C.Name as 'Employee',
    C.Salary as 'Salary'
from
    (
        select
        A.Name,A.Salary,A.DepartmentId,
        (select count(distinct B.Salary ) from Employee  as B where B.Salary >=A.Salary and B.DepartmentId =A.DepartmentId ) as rank
        from Employee  as A
    ) as C
    join Department as D
where C.DepartmentId = D.Id  and C.rank<=3;