题目地址:
https://leetcode.com/problems/employees-earning-more-than-their-managers/
给定一个Employee表,例如:
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
要求返回Salary比自己Manager更高的那些人的Name。例如上表需要返回:
+----------+
| Employee |
+----------+
| Joe |
+----------+
可以用FULL OUTER JOIN,也就是先将这个表与自己做笛卡儿积,然后选出ManagerId = Id和e1.Salary > e2.Salary的行的名字即可。代码如下:
SELECT e1.Name AS `Employee` FROM Employee e1 JOIN Employee e2
ON e1.ManagerId = e2.Id AND e1.Salary > e2.Salary
当然也可以这么写:
SELECT e1.Name AS `Employee` FROM Employee e1, Employee e2
WHERE e1.ManagerId = e2.Id AND e1.Salary > e2.Salary