[LeetCode][MySQL] 181. Employees Earning More Than Their Managers

Easy 🔗181. Employees Earning More Than Their Managers

📝문제 요약

Table: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| salary      | int     |
| managerId   | int     |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager.

Write a solution to find the employees who earn more than their managers.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input:
Employee table:
+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+
Output:
+----------+
| Employee |
+----------+
| Joe      |
+----------+
Explanation: Joe is the only employee who earns more than his manager.

✏️문제 풀이

SELECT

SELECT E1.name AS Employee

• Employee
  • E1.name : E1 테이블의 이름. 즉, 직원의 이름

FROM

FROM Employee E1 LEFT JOIN Employee E2 ON E1.managerId = E2.id

• Employee 테이블을 두번 참조
  • E1 : 직원들의 정보
  • E2 : 해당 직원의 매니저의 정보
• 두 데이터를 LEFT JOIN으로 병합하고 두 테이블의 외래키는 E1의 managerId와 E2의 id

WHERE

WHERE E1.salary > E2.salary

• E1.salary > E2.salary
  • 직원의 급여가 해당 직원의 매니저의 급여보다 많은 경우의 데이터만 필터링

💯제출 코드

SELECT E1.name AS Employee
FROM Employee E1 LEFT JOIN Employee E2 ON E1.managerId = E2.id
WHERE E1.salary > E2.salary