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

心得:

題目要求找出有哪些人的薪水比主管高,找出名字並修改欄位名稱為Employee

題目:

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

+----+-------+--------+-----------+
>
> | Id | Name | Salary | ManagerId |
>
> +----+-------+--------+-----------+
>
> | 1 | Joe | 70000 | 3 |
>
> | 2 | Henry | 80000 | 4 |
>
> | 3 | Sam | 60000 | NULL |
>
> | 4 | Max | 90000 | NULL |
>
> +----+-------+--------+-----------+
>
>

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

1
2
3
4
5
6
7
8
9
10
11
12

+----------+
>
> | Employee |
>
> +----------+
>
> | Joe |
>
> +----------+
>
>

答案:

  1. Sub Select
1
2
3
4
5
6
7

# Write your MySQL query statement below
SELECT a.Name AS Employee
FROM Employee AS a
WHERE a.Salary > (SELECT z.Salary
FROM Employee AS z
WHERE z.Id = a.ManagerId);
  1. Join
1
2
3
4
5
6
7

# Write your MySQL query statement below
SELECT a.Name AS Employee
FROM Employee AS a
JOIN Employee AS b
ON a.ManagerId = b.Id
WHERE a.Salary > b.Salary
  1. From
1
2
3
4
5
6

# Write your MySQL query statement below
SELECT a.Name AS Employee
FROM Employee AS a, Employee AS b
WHERE a.ManagerId = b.Id
AND a.Salary > b.Salary