LeetCode MySQL-181. Employees Earning More Than Their Managers

    AllenLi
    LeetCode MySQL

    Write an SQL query to find the employees who earn more than their managers.
    Return the result table in any order.

    寫一個 SQL Query來尋找賺的比老闆多的員工。
可任意排序結果。

    Table: Employee

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

    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.

    Solution:
    1. 選擇標題 t1.name 為 Employee。
    2. 將 Employee 拆為 t1, t2 兩張表進行比較。
    3. 以 t1.managerId 為基準，且符合 t2.id 中有 salary 比老闆高者。

    Code.1:

    SELECT t1.name AS Employee
	FROM Employee AS t1
LEFT JOIN Employee AS t2
	ON t1.managerId = t2.id
WHERE t1.salary > t2.salary;

    Code.2:

    SELECT t1.name AS Employee
    FROM Employee AS t1
    INNER JOIN Employee AS t2
        ON t1.managerId = t2.id
    WHERE t1.salary > t2.salary;

    newEmployeesEarning

    Related Posts