Leetcode: 185. all employees with the top three highest wages in the Department

The original title is ：https://leetcode-cn.com/problems/department-top-three-salaries/

Personal solution ：

$$benefitusewindowmouthLetterCount：$$

# Write your MySQL query statement below
select d.Name as Department, e2.Name as Employee, Salary 
from (
    select Name, Salary, DepartmentId
    from (
        select *, dense_rank() over(partition by DepartmentId order by Salary desc) as ranking
        from Employee
    ) as temp
    where ranking < 4
) as e2
left join Department d
on e2.DepartmentId = d.Id
where d.Id IS NOT NULL;

Not efficient enough ：

Official solution ：

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;

The official solution is ingenious where The logic in the clause , use e1 Of Salary Do external circulation ,e2 Of Salary Do internal circulation , So as to determine the qualified e1.Salary, Then the qualified employees are determined .

however , Whoa ！, I didn't expect the official efficiency to be worse than my own … Only 12.36%

follow-up ：

I went back and ran my code again （ Code of personal solution ）, Found defeated 99.52% Users of , What about this ？ The sky is confused and the earth is confused , You are ignorant and I am ignorant , Is really ignorant ！