leetcode训练营二天
1. 超过经理收入的员工
解答1:
select a.Name Employee from Employee a where a.ManagerId is not null AND a.Salary >(
select b.Salary from Employee b where a.ManagerId =b.Id
)
- 使用
is not null
的方式 去除是经理没有上司的员工 - 通过子查询 查找员工工资高于经理的员工:
- 缺点效率很低 但是好思考
解答2:通过inner join...on..
的方式来进行查询 效率很高
# 使用inner join ...on 会把a.managerid = b.id 相同的=的字段相同的进行查询出来
select a.name as Employee
from employee a inner join employee b on a.managerid = b.id
and a.salary > b.salary
推荐学习连接,嘻嘻保存:mysql内连接 左连接 右连接
2. 找出重复的子邮箱
题解1:
- 合理的使用聚合函数count 与group by 的结合
- 利用
having
对查询出的视图再进行添加条件
SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email)>1
推荐博客—
where group by order by的使用顺序
题解2:
- 通过
distinct
来排除自己 并且通过id不相等来找寻重复次数>2的
select distinct a.Email
from Person a,Person b
where a.Email=b.Email
and a.Id<>b.Id;
3. 从不订购客户
题解1:
select a.Name as 'Customers' from Customers as a
where a.id not in
(select CustomerId from Orders )
题解2:
- 通过左连接 找出 以左cumstomers为主的数据
- 通过 is null 得到没有订单的用户name
select A.Name as Customers
from Customers A left join Orders B
on A.Id = B.CustomerId
where B.Id is null