7/31 每日一題 (MYSQL 找出薪水高於其直屬上司的員工)

 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.

# Write your MySQL query statement below

SELECT E1.name AS Employee

FROM Employee E1

INNER JOIN Employee E2

ON E1.managerId = E2.id

WHERE E1.salary > E2.salary;

#SELECT E1.name AS Employee 這部分是表查詢的結果列,使用SELECT指定要返回的欄位

#從Employee表 簡稱E1 選擇欄位name,用AS把欄位取別名為Employee ,

#自連接了 同樣的Employee表改稱E2

#其中連接的基準用 Employee表的 E1.managerId = E2.id

#找到E1表的經理ID然後查對應到的E2表的員工ID, 這樣連接起來 就是員工與經理的配對

#WHERE E1.salary > E2.salary; 找出工資高於其經理的員工

合併資料庫

------------------------------------下面合併表格 看 員工與對應的經理資料庫

| id | name | salary | managerId | id | name | salary | managerId |
| -- | ----- | ------ | --------- | -- | ---- | ------ | --------- |
| 1 | Joe | 70000 | 3 | 3 | Sam | 60000 | null |
| 2 | Henry | 80000 | 4 | 4 | Max | 90000 | null |

--------------------------

--------------------------------------------

Output

| Employee | | -------- | | Joe |

---------------------參考解答

# Write your MySQL query statement below
select b.name as Employee from Employee as a JOIN employee as b 
ON a.id = b.managerId AND a.salary<b.salary