49 - 超过经理收入的员工

题目

Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, ManagerId int)
Truncate table Employee
insert into Employee (Id, Name, Salary, ManagerId) values ('1', 'Joe', '70000', '3')
insert into Employee (Id, Name, Salary, ManagerId) values ('2', 'Henry', '80000', '4')
insert into Employee (Id, Name, Salary, ManagerId) values ('3', 'Sam', '60000', 'None')
insert into Employee (Id, Name, Salary, ManagerId) values ('4', 'Max', '90000', 'None')

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

+----+-------+--------+-----------+ | 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 | +----------+

解答

通过id找到对应的manager，然后对比他们的salary

where

作者：LeetCode 链接：https://leetcode-cn.com/problems/two-sum/solution/chao-guo-jing-li-shou-ru-de-yuan-gong-by-leetcode/

select 
a.Name as 'Employee' 
from employee as a, employee as b
where a.`ManagerId`=b.`Id` and a.`Salary`>b.`Salary`

Runtime: 320 ms, faster than 52.18% of MySQL online submissions forEmployees Earning More Than Their Managers.

Memory Usage: N/A

这是用同一个表做了两次select，结果是这个表每个条目的排列组合：

SELECT *
FROM Employee AS a, Employee AS b

在这个基础上，再用where筛选

SELECT
    *
FROM
    Employee AS a,
    Employee AS b
WHERE
    a.ManagerId = b.Id
        AND a.Salary > b.Salary

最后再让输出的内容加一个别名。

神奇的是，这个能在定义别名之前，先用a。估计解析的时候，是先解析的from，再解析的select。

优化

作者：dong-fang-xu-ri 链接：https://leetcode-cn.com/problems/two-sum/solution/shi-yong-is-not-null-xian-qu-chu-yi-xie-bu-cun-zai/

判断条件多判断一下manageId是否存在。

select a.Name as Employee
from Employee as a ,Employee as b
where a.ManagerId is not null and 
a.ManagerId = b.Id 
and a.Salary > b.Salary;

Runtime: 299 ms, faster than 83.22% of MySQL online submissions forEmployees Earning More Than Their Managers.

Memory Usage: N/A

join

select两张表，相当于cross join

select a.Name as 'Employee' 
from 
    employee as a 
cross join 
    employee as b 
on a.`ManagerId`=b.`Id` and a.`Salary` > b.`Salary`

Runtime: 336 ms, faster than 43.06% of MySQL online submissions forEmployees Earning More Than Their Managers.

Memory Usage: N/A

好像用on和where效果是一样的

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

Runtime: 311 ms, faster than 63.59% of MySQL online submissions forEmployees Earning More Than Their Managers.

Memory Usage: N/A

优化

select 
    a.name as Employee 
from 
    employee as a 
join employee as b 
on a.`ManagerId` is not null and a.`ManagerId`=b.`Id` and a.`Salary` > b.`Salary`

Runtime: 289 ms, faster than 95.16% of MySQL online submissions forEmployees Earning More Than Their Managers.

Memory Usage: N/A

似乎join比where更加高效