181. Employees Earning More Than Their Managers

題目敘述

SQL Schema >

Create table If Not Exists Employee (id int, name varchar(255), salary int, managerId int)
Truncate table Employee
insert into Employee (id, name, salary, managerId) values ('1', 'Joe', '70000', '3')
insert into Employee (id, name, salary, managerId) values ('2', 'Henry', '80000', '4')
insert into Employee (id, name, salary, managerId) values ('3', 'Sam', '60000', 'None')
insert into Employee (id, name, salary, managerId) values ('4', 'Max', '90000', 'None')

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.

Write an SQL query to find the employees who earn more than their managers.

Return the result table in any order.

The query 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.

題目翻譯

從資料表 Employee 中找出誰的 salary 會比自己的 manager 高。

解法解析

因為要找出自己的 manager，可以用取兩次 Employee 的方式組一張大表，但是這樣效能不好。所以採用 JOIN 的方式來解決此題目。題目太簡單了，我都不知道還要怎麼掰字數了。反正就是用 JOIN。

解法範例

MySQL

# Write your MySQL query statement below
SELECT
     a.name AS Employee
FROM
     Employee AS a
     JOIN Employee AS b ON a.managerId = b.id
     AND a.salary > b.salary;