[LeetCode] 181. Employees Earning More Than Their Managers

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

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.

Employee
Joe

---

解题过程：
SQL Server：

with A as (select *from Employee where ManagerId is not null),
B as (select *from Employee where ManagerId is null)
select A.Name as Employee 
from A cross join B
where A.ManagerId=B.Id and A.Salary>B.Salary

通过with as语句创建临时表，cross join 完成两张表的笛卡尔乘积

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

这样就可以用A.ManagerId=B.Id and A.Salary>B.Salary进行查询了。

MySQL：
考虑到MySQL没有with这个关键词，我们不妨用自连接来将两张表合并。

SELECT
     a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
     ON a.ManagerId = b.Id
     AND a.Salary > b.Salary
;