[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 Bwhere 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 EmployeeFROM Employee AS a JOIN Employee AS b     ON a.ManagerId = b.Id     AND a.Salary > b.Salary;