LeetCode 181:Employees Earning More Than Their Managers
【Description】
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
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.
【Solution】
Personal solution:
with temp as (
select *
from Employee
where ManagerId is not NULL
)
select temp.Name as Employee
from temp,Employee
where temp.ManagerId=Employee.Id
and temp.Salary>Employee.Salary
Reference solution:
SELECT
a.Name AS 'Employee'
FROM
Employee AS a,
Employee AS b
WHERE
a.ManagerId = b.Id
AND a.Salary > b.Salary
;
SELECT
a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
ON a.ManagerId = b.Id
AND a.Salary > b.Salary
;