Employee表列出了包括经理在内的所有雇员,每一个雇员都有一个Id,同时表中存在一列ManagerId
+----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+
给定一个Employee列表,写一个SQL语句查询所有薪资高于经理的雇员。上表中,Joe是唯一薪资超过经理的雇员
+----------+ | Employee | +----------+ | Joe | +----------+
1:利用子查询先将不是的经理的雇员选出来,归为表a,然后将表a和原表进行比较查询
SELECT a.Name AS Employee
FROM (SELECT Name, Salary, ManagerId FROM Employee WHERE ManagerId IS NOT NULL) AS a, Employee AS b
WHERE a.ManagerId=b.Id AND a.Salary>b.Salary
参考他人
SELECT a.Name AS Employee
FROM Employee AS a, Employee AS b
WHERE a.ManagerId=b.Id AND a.Salary>b.Salary
算法题来自:https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/description/