[SQL]LeetCode185. 部门工资前三高的员工 | Department Top Three Salaries

★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
➤微信公众号：山青咏芝（shanqingyongzhi）
➤博客园地址：山青咏芝（https://www.cnblogs.com/strengthen/）
➤GitHub地址：https://github.com/strengthen/LeetCode
➤原文地址：https://www.cnblogs.com/strengthen/p/10172119.html 
➤如果链接不是山青咏芝的博客园地址，则可能是爬取作者的文章。
➤原文已修改更新！强烈建议点击原文地址阅读！支持作者！支持原创！
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★

热烈欢迎，请直接点击！！！

进入博主App Store主页，下载使用各个作品！！！

注：博主将坚持每月上线一个新app！！！

SQL 架构

Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int)
Create table If Not Exists Department (Id int, Name varchar(255))
Truncate table Employee
insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '70000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Henry', '80000', '2')
insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Sam', '60000', '2')
insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Max', '90000', '1')
Truncate table Department
insert into Department (Id, Name) values ('1', 'IT')
insert into Department (Id, Name) values ('2', 'Sales')

---

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   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 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.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

---

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

---

302ms

# Write your MySQL query statement below

SELECT D.Name AS Department,E.Name as Employee,Salary
FROM (
SELECT IF(@pre=DepartmentId,IF(@pre_salary<=>@pre_salary:=Salary,@rank,@rank:=@rank+1),@rank:=1) AS Rank,
    Name,Salary,@pre:=DepartmentId AS DepartmentId
FROM Employee,(SELECT @pre:=0,@rank:=0,@pre_salary:=NULL) v
ORDER BY DepartmentId,Salary DESC
) E
INNER JOIN Department D
ON E.DepartmentId=D.Id
WHERE Rank<=3

---

309ms

select d.Name as Department, e.Name as Employee, Salary
from Employee e join Department d on e.DepartmentId = d.Id
where 3 >= (
    select count(*)
    from (select distinct DepartmentId as dept_id, Salary from Employee) as e1
    where e1.dept_id = e.DepartmentId and e1.salary >= e.salary
)
order by Department, e.Salary desc

---

323ms

# Write your MySQL query statement below

# 99.7%
select d.Name Department, e.Name Employee, e.Salary
from (  select @rn := case when @dept = DepartmentId and @sal =  Salary then @rn 
                           when @dept = DepartmentId and @sal <> Salary then @rn + 1
                           else 1 end  rows,
               @dept := DepartmentId, @sal := Salary, employee.*
        from employee, (select @rn := 0, @dept := 0, @sal:= -1) t
        order by DepartmentId, Salary desc) e,
      Department d
where d.Id = e.DepartmentId
and e.rows <= 3
order by Department, Salary desc;

---

325ms

# Write your MySQL query statement below
select Name as Department, Employee, Salary
from (
        select Name as Employee, Salary, DepartmentId as Id,
            @rank := if(@id = (@id := DepartmentId), @rank, 0)
                + (@salary <> (@salary := Salary)) as Rank
        from Employee, (select @rank := 0, @id := -1, @salary := -1) init
        order by DepartmentId, Salary desc
    ) as rankek join Department using (Id)
where Rank <= 3