Leetcode Database Title: Employees with the highest salaries in the Department and all employees with the top three salaries in the Department

Look at these two leetcode database topics today

 

 

First:

The Employee table contains all employee information, and each employee has its corresponding Id, salary and department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+


Department table contains information about all departments of the company.

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


Write an SQL query to find the highest paid employees in each department. For example, according to the given table, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

 

Answer

The ranking function is used to rank the salary departments of each person first.

SELECT 
    Name,
    Salary,
    DepartmentId,
    dense_rank() over (partition by DepartmentId order by Salary desc)
FROM
    Employee

(2) After getting the ranking information, we are still short of a department name, so we need to connect the Department table with the table above.

SELECT
    b.Name Department,
    a.Name Employee,
    a.Salary Salary
FROM
    (
        SELECT 
            Name,
            Salary,
            DepartmentId,
            dense_rank() over (partition by DepartmentId order by Salary desc) as rank
        FROM
            Employee
    ) a JOIN Department b on a.DepartmentId = b.Id
WHERE
    a.rank = 1  # Find out the first of all departments.

Gone! Look at the rankings, but it takes MS SQL Server to use window functions. Mysql of leetcode is not yet supported. It has to be over 8.0 to support it.

 

Using variables and IF statements to implement dense_rank()

SELECT
    b.Name Department,
    a.Name Employee,
    a.Salary Salary
FROM
    (
        SELECT 
            Name,
            Salary,
            DepartmentId,
            if(@pre_DepartmentId = DepartmentId,
              if(@pre_Salary = Salary,@cur_rank, @cur_rank := @cur_rank + 1),
              @cur_rank:=1) as rank
            @pre_Salary := Salary,
            @pre_DepartmentId := DepartmentId
        FROM
            Employee,(SELECT @cur_rank := 0,@pre_DepartmentId := null,@pre_Salary := null)
        ORDER BY
            DepartmentId,
            Salary
    ) a JOIN Department b on a.DepartmentId = b.Id
WHERE
    a.rank = 1

 

The second way:

If you know the first way, the second way will be natural. It will be in(1,2,3).

SELECT 
    b.Name Department,
    a.Name Employee,
    a.Salary Salary
From (
        SELECT 
            Name,
            Salary,
            DepartmentId, 
            DENSE_RANK() over(PARTITION BY DepartmentId ORDER BY Salary Desc) AS rank
        From 
            Employee
) As a join Department b on a.DepartmentId =b.Id
Where 
    a.rank in(1,2,3)

Tags: SQL Database MySQL

Posted on Tue, 08 Oct 2019 18:24:16 -0700 by MadDawgX