Q:
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.
找出职员中工资比他们经理高的员工,并开除。。。大误
A:
最直接也最简单的思路就是把Employee表声明为两个不同的表,然后对它们进行比较,语句如下:
select A.Name
from Employee as A, Employee as B
where A.ManagerId = B.Id and A.Salary > B.Salary
另一种思路是声明两个不同的表然后自然连接起来,和上面的比较类似
Select emp.Name from
Employee emp inner join Employee manager
on emp.ManagerId = manager.Id
where emp.Salary > manager.Salary
这两种是比较容易想到的,看LeetCode上面还有大神使用下面的语句来查询
select e1.Name
from Employee e1
where e1.ManagerId IS NOT NULL AND e1.Salary > (Select e2.Salary
from Employee e2
where e1.ManagerId = e2.Id)
这种也是可行的,但是稍显复杂了些,而且没有前两个直观、易于理解。
总结:
要多掌握SQL语句的各种操作命令,并且要加强分析问题的能力,提高逻辑思维能力。