LeetCode MySQL-185. Department Top Three Salaries

    AllenLi
    LeetCode MySQL

    A company’s executives are interested in seeing who earns the most money in each of the company’s departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.
    Write an SQL query to find the employees who are high earners in each of the departments.
    Return the result table in any order.

    一個公司的主管對於哪個部門中高收入的人感到興趣，
部門中高收入的人是在部門中排在前三名。
寫一個SQL查詢來找出每個部門高收入的人。
可任意排序結果。

    Table: Employee

    +--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id is the primary key column for this table.
departmentId is a foreign key of the ID from the Department table.
Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.

    Table: Department

    +-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the ID of a department and its name.

    Example 1:

    Input: 
Employee table:
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+
Output: 
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Joe      | 85000  |
| IT         | Randy    | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+
Explanation: 
In the IT department:
- Max earns the highest unique salary
- Both Randy and Joe earn the second-highest unique salary
- Will earns the third-highest unique salary

In the Sales department:
- Henry earns the highest salary
- Sam earns the second-highest salary
- There is no third-highest salary as there are only two employees

    Solution:
    1. 選擇標題 t2.name 為 Department，t1.name 為 Employee，t1.salary 為 Salary。
    2. 分別建立 Employee 為 t1，Department 為 t2。
    3. 尋找 salary 不重複數值的次數為 6，
    然後將複製相同的 salary 進行比對，
    同時將部門進行分類，並找出各部門該員工收入個數不大於 3 的所有員工。

    +----+-------+--------+--------------+------------------+-----------------+
| id | name  | salary | departmentId | *DISTINCT(COUNT) | *>salary(COUNT) |
+----+-------+--------+--------------+------------------+-----------------+
| 1  | Joe   | 85000  | 1            | 1                | 1               |
| 4  | Max   | 90000  | 1            | 1                | 0               | 
| 5  | Janet | 69000  | 1            | 1                | 3               |
| 6  | Randy | 85000  | 1            | 0                | 1               |
| 7  | Will  | 70000  | 1            | 1                | 2               |
+----+-------+--------+--------------+------------------+-----------------+
*DISTINCT(COUNT) = 4
*>salary(COUNT) < 3 = 90000, 85000, 85000, 70000

+----+-------+--------+--------------+------------------+-----------------+
| id | name  | salary | departmentId | *DISTINCT(COUNT) | *>salary(COUNT) |
+----+-------+--------+--------------+------------------+-----------------+
| 2  | Henry | 80000  | 2            | 1                | 0               |
| 3  | Sam   | 60000  | 2            | 1                | 1               |
+----+-------+--------+--------------+------------------+-----------------+
*DISTINCT(COUNT) = 2
*>salary(COUNT) < 3 = 80000, 60000

    Code.1:

    SELECT t2.name AS Department,
	t1.name AS Employee,
    t1.salary AS Salary
    FROM Employee AS t1
LEFT JOIN Department AS t2
	ON t1.departmentId = t2.id
WHERE (SELECT COUNT(DISTINCT salary) 
		FROM Employee
        WHERE salary > t1.salary
			AND DepartmentId = t2.id) < 3
		ORDER BY t2.name, t1.salary DESC;

    newTopThree

    Related Posts