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

Answer:
思路:一个表当2个表用。

SELECT
    E1.Name AS 'Employee'
FROM
    Employee AS E1,
    Employee AS E2 
WHERE
    E1.ManagerId = E2.Id 
    AND E1.Salary > E2.Salary

分析:

SELECT
    *
FROM
    Employee AS E1,
    Employee AS E2 

的结果如下(2个表相乘,4 * 4 = 16个结果,4 + 4 =8个字段):
这里写图片描述
接着,

SELECT
    *
FROM
    Employee AS E1,
    Employee AS E2 
WHERE
    E1.ManagerId = E2.Id 

结果如下(剩下2个结果了):
这里写图片描述
接着,

SELECT
    *
FROM
    Employee AS E1,
    Employee AS E2 
WHERE
    E1.ManagerId = E2.Id 
    AND E1.Salary > E2.Salary

结果如下(剩下1个结果了):
这里写图片描述
最后,将*改为E1.Name AS 'Employee',取结果中的Name字段。
这里写图片描述

Finally,附上employee表的sql:
这里写图片描述

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', NULL);
insert into Employee (Id, Name, Salary, ManagerId) values ('4', 'Max', '90000', NULL);
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值